CREATE TABLE

Create a table.

Syntax:

   CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (
      column datatype [DEFAULT expr] [column_constraint(s)[,…]] [,column datatype [,…]] )
         [table_constraint [,…]]
           [table_ref_constraint [,…]]
              [ON COMMIT {DELETE|PRESERVE} ROWS]
                 storage_options  [COMPRESS int|NOCOMPRESS]
                [LOB_storage_clause][varray_clause][nested_storage_clause] [XML_type_clause]
                    Partitioning_clause
                       [[NO]CACHE] [[NO]ROWDEPENDENCIES] [[NO]MONITORING] [PARALLEL parallel_clause]
                          [ENABLE enable_clause | DISABLE disable_clause]
                             {ENABLE|DISABLE} ROW MOVEMENT
                                [AS subquery]

   CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (
      column datatype [DEFAULT expr] [column_constraint(s)[,…]] [,column datatype [,…]] )
         [table_constraint [,…]]
           [table_ref_constraint [,…]]
              [ON COMMIT {DELETE|PRESERVE} ROWS]
                 CLUSTER cluster_name (col1, col2,… )
                [LOB_storage_clause][varray_clause][nested_storage_clause] [XML_type_clause]
                    Partitioning_clause
                       [[NO]CACHE] [[NO]ROWDEPENDENCIES] [[NO]MONITORING] [PARALLEL parallel_clause]
                          [ENABLE enable_clause | DISABLE disable_clause]
                             {ENABLE|DISABLE} ROW MOVEMENT
                                [AS subquery] 

   CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (
      column datatype [DEFAULT expr] [column_constraint(s)] [,column datatype [,…]]] )
         [table_constraint [,…]]
           [table_ref_constraint [,…]]
              [ON COMMIT {DELETE|PRESERVE} ROWS]
                 ORGANIZATION {HEAP [storage_options] [COMPRESS int|NOCOMPRESS]
                              | INDEX idx_organized_tbl_clause
                              | EXTERNAL external_table_clause }
                [LOB_storage_clause][varray_clause][nested_storage_clause] [XML_type_clause]
                    Partitioning_clause
                       [[NO]CACHE] [[NO]ROWDEPENDENCIES] [[NO]MONITORING] [PARALLEL parallel_clause]
                          [ENABLE enable_clause | DISABLE disable_clause]
                             {ENABLE|DISABLE} ROW MOVEMENT
                                [AS subquery] 

   CREATE TABLE [schema.]table OF XMLTYPE [XML_type_clause]

storage_options:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   [LOGGING|NOLOGGING]

idx_organized_tbl_clause:
   storage_option(s) 
   {MAPPING TABLE | NOMAPPING}
   [PCTTHRESHOLD int]
   [COMPRESS int|NOCOMPRESS]
   [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

nested_storage_clause:
   NESTED TABLE {nested_item | COLUMN_VALUE}
      [ [ELEMENT] IS OF TYPE (ONLY type) ]] |  [ [NOT] SUBSTITUTABLE AT ALL LEVELS ]] 
      STORE AS storage_table 
         [RETURN AS {LOCATOR|VALUE} ]

XML_type_clause:
   [XMLTYPE [COLUMN] column [STORE AS OBJECT RELATIONAL] ]
       [[XMLSCHEMA xmlschema_URL] ELEMENT {element |xmlschema_URL#element}]

   [XMLTYPE [COLUMN] column [STORE AS CLOB LOB_Segname (LOB_Params)]]
       [[XMLSCHEMA xmlschema_URL] ELEMENT {element |xmlschema_URL#element}]

   [XMLTYPE [COLUMN] column [STORE AS CLOB LOB_Params] ]
       [[XMLSCHEMA xmlschema_URL] ELEMENT {element |xmlschema_URL#element}]

external_table_clause:
   ([TYPE access_driver_type]
      DEFAULT DIRECTORY directory [ACCESS PARAMETERS {USING CLOB subquery | (opaque_format_spec) }]
         LOCATION (directory:'location_specifier' [,directory2:'location_specifier2'…)
         ) [REJECT LIMIT {int|UNLIMITED}]

Missing from this page are the options for creating OBJECT TABLES - see the Oracle docs for this.

To create a table requires the CREATE TABLE privilege plus enough quota on the tablespace where the table is to be created.

Examples

create table SIMPLE (MY_NUM number primary key);

create table COPY_OF_EMP as
select * from EMP;

create table EMPTY_COPY as
select * from EMP where 1 = 0;


create table ACCOUNTS(
AC_ID_PK number primary key,
AC_STATUS number,
AC_COUNTRY_ID number default 44,
AC_CREATED date default sysdate,
AC_ACCOUNT varchar2(50)
)
tablespace DATA;


create table SALES(
SA_ID_PK number primary key,
SA_PRODUCT_ID number not null,
SA_DATE_PART date not null,
SA_COST number (12,2) not null
)
partition by range (SA_DATE_PART) (
partition P01_JAN values less than (to_date('2010-02-01','yyyy-mm-dd')),
partition P02_FEB values less than (to_date('2010-03-01','yyyy-mm-dd')),
partition P03_MAR values less than (to_date('2010-04-01','yyyy-mm-dd')),
partition P04_APR values less than (to_date('2010-05-01','yyyy-mm-dd')),
partition P05_REST values less than (maxvalue)
);


“Make everything as simple as possible, but not simpler” ~ Albert Einstein


Related:

ALTER INDEX
ALTER VIEW
COMMENT - Add a comment to a table or a column.

  V$RESERVED_WORDS 
  DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
  DBA_TABLES           ALL_TABLES           USER_TABLES          TAB
  DBA_PART_TABLES      ALL_PART_TABLES      USER_PART_TABLES   


© Copyright SS64.com 1999-2013
Some rights reserved