SQL Statements (continued), 9 of 11

CREATE TABLE

Syntax

relational_table:

object_table:

relational_properties::=

object properties::=

physical_properties::=

table_properties::=

subquery::= See "SELECT and Subqueries".

table_constraint, column_constraint, table_ref_constraint, column_ref_constraint, constraint_state: See the "constraint_clause"

OID_clause::=

OID_index_clause::=

segment_attributes_clause::=

row_movement_clause::=

physical_attributes_clause::=

storage_clause: See the "storage_clause".

index_organized_table_clause::=

compression_clause::=

index_organized_overflow_clause::=

LOB_storage_clause::=

LOB_parameters::=

varray_storage_clause::=

nested_table_storage_clause::=

range_partitioning_clause::=

composite_partitioning_clause::=

partition_definition::=

subpartition_clause::=

partition_level_subpartitioning::=

hash_partitioning_clause::=

hash_partitioning_storage_clause::=

parallel_clause::=

enable_disable_clause::=

using_index_clause::=

global_index_clause::=

global_partition_clause::=

Purpose

To create a relational table, the basic structure to hold user data.

To create an object table or a table that uses an object type for a column definition. An object table is a table explicitly defined to hold object instances of a particular type.

You can also create an object type and then use it in a column when creating a relational table.

Tables are created with no data unless a query is specified. You can add rows to a table with the INSERT statement. After creating a table, you can define additional columns, partitions, and integrity constraints with the ADD clause of the ALTER TABLE statement. You can change the definition of an existing column or partition with the MODIFY clause of the ALTER TABLE statement.

See also:
Oracle8i Application Developer's Guide - Fundamentals and "CREATE TYPE" for more information about creating objects.

Prerequisites

To create a relational table in your own schema, you must have system privilege. To create a table in another user's schema, you must have CREATE ANY TABLE system privilege. Also, the owner of the schema to contain the table must have either space quota on the tablespace to contain the table or UNLIMITED TABLESPACE system privilege.

In addition to the table privileges above, to create a table that uses types, the owner of the table must have the EXECUTE object privilege in order to access all types referenced by the table, or you must have the EXECUTE ANY TYPE system privilege. These privileges must be granted explicitly and not acquired through a role.

Additionally, if the table owner intends to grant access to the table to other users, the owner must have been granted the EXECUTE privileges to the referenced types with the GRANT OPTION, or have the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. Without these privileges, the table owner has insufficient privileges to grant access on the table to other users.

To enable a UNIQUE or PRIMARY KEY constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table.

See Also:

"CREATE INDEX".

Oracle8i Application Developer's Guide - Fundamentals for more information about the privileges required to create tables using types.

Keywords and Parameters

Examples

General Example

To define the EMP table owned by SCOTT, you could issue the following statement:

CREATE TABLE scott.emp 
   (empno     NUMBER        CONSTRAINT pk_emp PRIMARY KEY, 
    ename     VARCHAR2(10)  CONSTRAINT nn_ename NOT NULL 
                            CONSTRAINT upper_ename 
   CHECK (ename = UPPER(ename)), 
       job        VARCHAR2(9), 
       mgr        NUMBER       CONSTRAINT fk_mgr 
                               REFERENCES scott.emp(empno), 
       hiredate  DATE          DEFAULT SYSDATE, 
       sal       NUMBER(10,2)  CONSTRAINT ck_sal 
   CHECK (sal > 500), 
          comm      NUMBER(9,0)   DEFAULT NULL, 
          deptno    NUMBER(2)     CONSTRAINT nn_deptno NOT NULL 
                                  CONSTRAINT fk_deptno 
                                  REFERENCES scott.dept(deptno) ) 
   PCTFREE 5 PCTUSED 75;

This table contains eight columns. The EMPNO column is of datatype NUMBER and has an associated integrity constraint named PK_EMP. The HIRDEDATE column is of datatype DATE and has a default value of SYSDATE, and so on.

This table definition specifies a PCTFREE of 5 and a PCTUSED of 75, which is appropriate for a relatively static table. The definition also defines integrity constraints on some columns of the EMP table.

Temporary Table Example

The following statement creates a temporary table FLIGHT_SCHEDULE for use in an automated airline reservation scheduling system. Each client has its own session and can store temporary schedules. The temporary schedules are deleted at the end of the session.

CREATE GLOBAL TEMPORARY TABLE flight_schedule (
   startdate DATE, 
   enddate DATE, 
   cost NUMBER)
   ON COMMIT PRESERVE ROWS;

Storage Example

To define the sample table SALGRADE in the HUMAN_RESOURCE tablespace with a small storage capacity and limited allocation potential, issue the following statement:

CREATE TABLE salgrade 
   ( grade  NUMBER  CONSTRAINT pk_salgrade 
                    PRIMARY KEY 
                    USING INDEX TABLESPACE users_a, 
     losal  NUMBER, 
     hisal  NUMBER ) 
   TABLESPACE human_resource 
   STORAGE (INITIAL     6144  
            NEXT        6144 
            MINEXTENTS     1  
            MAXEXTENTS     5 );

The above statement also defines a primary key constraint on the GRADE column and specifies that the index Oracle creates to enforce this constraint is created in the USERS_A tablespace.

See Also:
The "constraint_clause" for more examples of defining integrity constraints.

PARALLEL Example

The following statement creates a table using an optimum number of parallel execution servers to scan SCOTT.EMP and to populate EMP_DEPT:

CREATE TABLE emp_dept
   PARALLEL
   AS SELECT * FROM scott.emp
   WHERE deptno = 10;

Using parallelism speeds up the creation of the table because Oracle uses parallel execution servers to create the table. After the table is created, querying the table is also faster, because the same degree of parallelism is used to access the table.

NOPARALLEL Example

The following statement creates a table serially. Subsequent DML and queries on the table will also be serially executed.

CREATE TABLE emp_dept
   AS SELECT * FROM scott.emp
   WHERE deptno = 10;

ENABLE VALIDATE Example

The following statement creates the DEPT table, defines a primary key constraint, and places it in ENABLE VALIDATE state:

CREATE TABLE dept
   (deptno  NUMBER (2) PRIMARY KEY,
    dname   VARCHAR2(10),
    loc     VARCHAR2(9) )
   TABLESPACE user_a;

DISABLE Example

The following statement creates the DEPT table and defines a disabled primary key constraint:

CREATE TABLE dept
   (deptno   NUMBER (2)   PRIMARY KEY DISABLE,
    dname    VARCHAR2(10),
    loc      VARCHAR2(9) );

EXCEPTIONS INTO Example

The following example creates the ORDER_EXEPTIONS table to hold rows from an index-organized table ORDERS that violate integrity constraint CHECK_ORDERS:

CREATE TABLE orders
   (ord_num  NUMBER PRIMARY KEY,
    ord_quantity NUMBER)
   ORGANIZATION INDEX;

EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE
   ('SCOTT', 'ORDERS', 'ORDER_EXCEPTIONS');

ALTER TABLE orders
   ADD CONSTRAINT CHECK_ORDERS CHECK (ord_quantity > 0)
   EXCEPTIONS INTO ORDER_EXCEPTIONS;

To specify an exception table, you must have the privileges necessary to insert rows into the table. To examine the identified exceptions, you must have the privileges necessary to query the exceptions table.

See Also:

"INSERT".

"SELECT and Subqueries" for information on the privileges necessary to insert rows into tables.

Nested Table Example

The following statement creates relational table EMPLOYEE with a nested table column PROJECTS:

CREATE TABLE employee
   (empno NUMBER, name CHAR(31), projects PROJ_TABLE_TYPE)
   NESTED TABLE projects STORE AS nested_proj_table(
      (PRIMARY KEY (nested_table_id, pno)) ORGANIZATION INDEX)
   RETURN AS LOCATOR;

LOB Column Example

The following statement creates table LOB_TAB with two LOB columns and specifies the LOB storage characteristics:

CREATE TABLE lob_tab (col1 BLOB, col2 CLOB)
   STORAGE (INITIAL 256 NEXT 256)
   LOB (col1, col2) STORE AS
      (TABLESPACE lob_seg_ts
       STORAGE (INITIAL 6144 NEXT 6144)
       CHUNK 4000
       NOCACHE LOGGING);

In the example, Oracle rounds the CHUNK up to 4096 (the nearest multiple of the block size of 2048).

Index-Organized Table Example

The following statement creates an index-organized table:

CREATE TABLE docindex
  ( token                  CHAR(20),
    doc_oid                INTEGER,
    token_frequency        SMALLINT,
    token_occurrence_data  VARCHAR2(512),
       CONSTRAINT pk_docindex PRIMARY KEY (token, doc_oid) )
  ORGANIZATION INDEX TABLESPACE text_collection
  PCTTHRESHOLD 20 INCLUDING token_frequency
  OVERFLOW TABLESPACE text_collection_overflow;

Partitioned Table Example

The following statement creates a table with three partitions:

CREATE TABLE stock_xactions
      (stock_symbol CHAR(5),
       stock_series CHAR(1),
       num_shares NUMBER(10),
       price NUMBER(5,2),
       trade_date DATE)
   STORAGE (INITIAL 100K NEXT 50K) LOGGING
   PARTITION BY RANGE (trade_date)
     (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY'))
        TABLESPACE ts0 NOLOGGING,
      PARTITION sx1993 VALUES LESS THAN (TO_DATE('01-JAN-1994','DD-MON-YYYY'))
        TABLESPACE ts1,
      PARTITION sx1994 VALUES LESS THAN (TO_DATE('01-JAN-1995','DD-MON-YYYY'))
        TABLESPACE ts2);

For information about partitioned table maintenance operations, see the Oracle8i Administrator's Guide.

Partitioned Table with LOB Columns Example

This statement creates a partitioned table PT with two partitions P1 and P2, and three LOB columns, B, C, and D:

CREATE TABLE PT (A NUMBER, B BLOB, C CLOB, D CLOB)
   LOB (B,C,D) STORE AS (STORAGE (NEXT 20M))
   PARTITION BY RANGE (A) 
   (PARTITION P1 VALUES LESS THAN (10) TABLESPACE TS1
      LOB (B,D) STORE AS (TABLESPACE TSA STORAGE (INITIAL 20M)),
   PARTITION P2 VALUES LESS THAN (20)
      LOB (B,C) STORE AS (TABLESPACE TSB)
   TABLESPACE TSX;

Partition P1 will be in tablespace TS1. The LOB data partitions for B and D will be in tablespace TSA. The LOB data partition for C will be in tablespace TS1. The storage attribute INITIAL is specified for LOB columns B and D; other attributes will be inherited from the default table-level specification. The default LOB storage attributes not specified at the table level will be inherited from the tablespace TSA for columns B and D and tablespace TS1 for column C. LOB index partitions will be in the same tablespaces as the corresponding LOB data partitions. Other storage attributes will be based on values of the corresponding attributes of the LOB data partitions and default attributes of the tablespace where the index partitions reside.

Partition P2 will be in the default tablespace TSX. The LOB data for B and C will be in tablespace TSB. The LOB data for D will be in tablespace TSX. The LOB index for columns B and C will be in tablespace TSB. The LOB index for column D will be in tablespace TSX.

Hash-Partitioned Table Example

This statement creates a table partitioned by hash on columns containing data about chemicals. The hash partitions are stored in tablespaces TBS1, TBS2, TBS3, and TBS4:

CREATE TABLE exp_data (
      d DATE, temperature NUMBER, Fe2O3_concentration NUMBER,
      HCl_concentration NUMBER, Au_concentration NUMBER,
      amps NUMBER, observation VARCHAR(4000))
   PARTITION BY HASH (HCl_concentration, Au_concentration)
   PARTITIONS 32 STORE IN (tbs1, tbs2, tbs3, tbs4);

Composite-Partitioned Table Example

This statement creates a composite-partitioned table. The range partitioning facilitates data and partition pruning by sale date. The hash subpartitioning enables subpartition elimination for queries by a specific item number. Most of the partitions consist of 8 subpartitions. However, the partition covering the slowest quarter will have 4 subpartitions, and the partition covering the busiest quarter will have 16 subpartitions.

CREATE TABLE sales (item INTEGER, qty INTEGER, 
                    store VARCHAR(30), 
                    dept NUMBER, sale_date DATE)
   PARTITION BY RANGE (sale_date) 
   SUBPARTITION BY HASH(item)
   SUBPARTITIONS 8 
   STORE IN (tbs1, tbs2, tbs3, tbs4, tbs5, tbs6, tbs7, tbs8)
   (PARTITION q1_1997 
      VALUES LESS THAN (TO_DATE('01-apr-1997', 'dd-mon-yyyy')),
    PARTITION q2_1997 
      VALUES LESS THAN (TO_DATE('01-jul-1997', 'dd-mon-yyyy')),
    PARTITION q3_1997
      VALUES LESS THAN (TO_DATE('01-oct-1997', 'dd-mon-yyyy'))
      (SUBPARTITION q3_1997_s1 TABLESPACE ts1, 
       SUBPARTITION q3_1997_s2 TABLESPACE ts3, 
       SUBPARTITION q3_1997_s3 TABLESPACE ts5,
       SUBPARTITION q3_1997_s4 TABLESPACE ts7),
    PARTITION q4_1997 
      VALUES LESS THAN (TO_DATE('01-jan-1998', 'dd-mon-yyyy'))
      SUBPARTITIONS 16
      STORE IN (tbs1, tbs3, tbs5, tbs7, tbs8, tbs9, tbs10,
                tbs11),
    PARTITION q1_1998 
      VALUES LESS THAN (TO_DATE('01-apr-1998', 'dd-mon-yyyy')));

Object Table Examples

Consider object type DEPT_T:

CREATE TYPE dept_t AS OBJECT
   ( dname VARCHAR2(100), 
     address VARCHAR2(200) );

Object table DEPT holds department objects of type DEPT_T:

CREATE TABLE dept OF dept_t;

The following statement creates object table SALESREPS with a user-defined object type, SALESREP_T:

CREATE OR REPLACE TYPE salesrep_t AS OBJECT
  ( repId NUMBER,
    repName VARCHAR2(64));
CREATE TABLE salesreps OF salesrep_t;

Nested Table Example

The following statement creates relational table EMPLOYEE with a nested table column PROJECTS:

CREATE TABLE employee (empno NUMBER, name CHAR(31),
   projects PROJ_TABLE_TYPE)
   NESTED TABLE projects STORE AS nested_proj_table;

REF Example

The following example creates object type DEPT_T and object table DEPT to store instances of all departments. A table with a scoped REF is then created.

CREATE TYPE dept_t AS OBJECT
   ( dname   VARCHAR2(100),
     address VARCHAR2(200) );

CREATE TABLE dept OF dept_t;

CREATE TABLE emp
   ( ename   VARCHAR2(100),
     enumber NUMBER,
     edept   REF dept_t SCOPE IS dept );

The following statement creates a table with a REF column which has a referential constraint defined on it:

CREATE TABLE emp
   ( ename   VARCHAR2(100),
     enumber NUMBER,
     edept   REF dept_t REFERENCES dept);

User-Defined OID Example

This example creates an object type and a corresponding object table whose OID is primary key based:

CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30));
CREATE TABLE emp OF emp_t (empno PRIMARY KEY)
   OBJECT IDENTIFIER IS PRIMARY KEY;

You can subsequently reference the EMP object table in either of the following two ways:

CREATE TABLE dept (dno NUMBER
                   mgr_ref REF emp_t SCOPE IS emp);
CREATE TABLE dept (
    dno NUMBER,
    mgr_ref REF emp_t CONSTRAINT mgr_in_emp REFERENCES emp);

Constraints on Type Columns Example

CREATE TYPE address AS OBJECT
  ( hno    NUMBER,
    street VARCHAR2(40),
    city   VARCHAR2(20),
    zip    VARCHAR2(5),
    phone  VARCHAR2(10) );

CREATE TYPE person AS OBJECT
  ( name        VARCHAR2(40),
    dateofbirth DATE,
    homeaddress address,
    manager     REF person );

CREATE TABLE persons OF person
  ( homeaddress NOT NULL
      UNIQUE (homeaddress.phone),
      CHECK (homeaddress.zip IS NOT NULL),
      CHECK (homeaddress.city <> 'San Francisco') );

PARALLEL Example

The following statement creates a table using 10 parallel execution servers, 5 to scan SCOTT.EMP and another 5 to populate EMP_DEPT:

CREATE TABLE emp_dept
   PARALLEL (5)
   AS SELECT * FROM scott.emp
   WHERE deptno = 10;