| Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
| Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Purpose
Use the CREATE TABLE statement to create one of the following types of tables:
A relational table, which is the basic structure to hold user data.
An object table, which is a table that uses an object type for a column definition. An object table is 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 subquery 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:
Oracle Database Application Developer's Guide - Fundamentals, Oracle Database Administrator's Guide, and CREATE TYPE for more information about creating objects
ALTER TABLE and DROP TABLE for information on modifying and dropping tables
Additional Topics
Prerequisites
To create a relational table in your own schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you must have the 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 the UNLIMITED TABLESPACE system privilege.
In addition to these table privileges, to create an object table or a relational table with an object type column, 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, then the owner must have been granted the EXECUTE object privilege on the referenced types WITH GRANT OPTION, or have the EXECUTE ANY TYPE system privilege WITH ADMIN OPTION. Without these privileges, the table owner has insufficient privileges to grant access to 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 Database creates an index on the columns of the unique or primary key in the schema containing the table.
To create an external table, you must have the required read and write operating system privileges on the appropriate operating system directories. You must have the READ object privilege on the database directory object corresponding to the operating system directory in which the external data resides. You must also have the WRITE object privilege on the database directory in which the files will reside if you specify a log file or bad file in the opaque_format_spec or if you unload data into an external table from a database table by specifying the AS subquery clause.
See Also:
Oracle Database Application Developer's Guide - Fundamentals for more information about the privileges required to create tables using types
Syntax
create_table::=
(relational_table::=, object_table ::=, XMLType_table ::=)
Note:
Each of the clauses following the table name is optional for any given relational table. However, for every table you must at least specify either column names and datatypes using the
relational_properties clause or an AS subquery clause using the table_properties clause.(relational_properties::=, physical_properties::=, table_properties::=)
object_table ::=
(object_table_substitution ::=, object_properties::=, oid_clause::=, oid_index_clause::=, physical_properties::=, table_properties::=)
XMLType_table ::=
(XMLType_storage::=, XMLSchema_spec::=, oid_clause::=, oid_index_clause::=, physical_properties::=, table_properties::=)
(column_definition::=, constraint::=, supplemental_logging_props ::=)
column_definition::=
(encryption_spec::=, constraint::=)
encryption_spec::=
(constraint::=, supplemental_logging_props ::=)
oid_clause::=
(physical_attributes_clause::=)
(segment_attributes_clause ::=, table_compression::=, index_org_table_clause ::=, external_table_clause ::=)
(physical_attributes_clause::=, logging_clause::=)
(column_properties::=, table_partitioning_clauses ::=, parallel_clause ::=, enable_disable_clause ::=, row_movement_clause ::=, subquery::=)
(object_type_col_properties::=, nested_table_col_properties ::=, varray_col_properties ::=, LOB_storage_clause::=, LOB_partition_storage::=, XMLType_column_properties ::=)
substitutable_column_clause::=
nested_table_col_properties ::=
(substitutable_column_clause::=, object_properties::=, physical_properties::=, column_properties::=)
(substitutable_column_clause::=, LOB_parameters ::=)
LOB_parameters ::=
logging_clause::=
LOB_partition_storage::=
(LOB_storage_clause::=, varray_col_properties ::=)
(XMLType_storage::=, XMLSchema_spec::=)
XMLType_storage::=
XMLSchema_spec::=
(mapping_table_clauses::=, key_compression ::=, index_org_overflow_clause ::=)
key_compression ::=
(segment_attributes_clause ::=)
supplemental_logging_props ::=
supplemental_log_grp_clause::=
supplemental_id_key_clause::=
external_data_properties::=
(opaque_format_spec: See Oracle Database Utilities for information on how to specify values for the opaque_format_spec.)
table_partitioning_clauses ::=
(range_partitioning::=, hash_partitioning ::=, list_partitioning ::=, composite_partitioning ::=)
(range_values_clause::=, table_partition_description::=)
(individual_hash_partitions::=, hash_partitions_by_quantity::=)
(list_values_clause::=, table_partition_description::=)
(subpartition_by_list::=, subpartition_by_hash::=, range_values_clause::=, table_partition_description::=)
individual_hash_partitions::=
(partitioning_storage_clause::=)
hash_partitions_by_quantity::=
subpartition_by_list::=
(list_values_clause::=, partitioning_storage_clause::=)
range_values_clause::=
list_values_clause::=
table_partition_description::=
(segment_attributes_clause ::=, table_compression::=, LOB_storage_clause::=, varray_col_properties ::=, partition_level_subpartition::=)
partition_level_subpartition::=
subpartition_spec::=
(list_values_clause::=, partitioning_storage_clause::=)
partitioning_storage_clause::=
parallel_clause ::=
(using_index_clause ::=, exceptions_clause not supported in CREATE TABLE statements)
(create_index::=, index_properties::=)
index_properties::=
(global_partitioned_index ::=, local_partitioned_index ::=--part of CREATE INDEX, index_attributes::=, domain_index_clause: not supported in using_index_clause)
index_attributes::=
(physical_attributes_clause::=, logging_clause::=, key_compression ::=, parallel_clause: not supported in using_index_clause)
Semantics
Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions with appropriate privileges. The data in a temporary table is visible only to the session that inserts the data into the table.
When you first create a temporary table, its table metadata is stored in the data dictionary, but no space is allocated for table data. Space is allocated for the table segment at the time of the first DML operation on the table. The temporary table definition persists in the same way as the definitions of regular tables, but the table segment and any data the table contains are either session-specific or transaction-specific data. You specify whether the table segment and data are session- or transaction-specific with the ON COMMIT keywords.
You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.
See Also:
Oracle Database Concepts for information on temporary tables and "Temporary Table Example"Restrictions on Temporary Tables Temporary tables are subject to the following restrictions:
Temporary tables cannot be partitioned, clustered, or index organized.
You cannot specify any foreign key constraints on temporary tables.
Temporary tables cannot contain columns of nested table.
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.
Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.
You cannot specify the segment_attributes_clause, nested_table_col_properties, or parallel_clause.
Distributed transactions are not supported for temporary tables.
schema
Specify the schema to contain the table. If you omit schema, then the database creates the table in your own schema.
table
Specify the name of the table or object table to be created.
See Also:
"General Examples"relational_properties
The relational properties describe the components of a relational table.
column_definition
The column_definition lets you define the characteristics of the column.
Specify the name of a column of the table.
If you also specify AS subquery, then you can omit column and datatype unless you are creating an index-organized table. If you specify AS subquery when creating an index-organized table, then you must specify column, and you must omit datatype.
The absolute maximum number of columns in a table is 1000. However, when you create an object table or a relational table with columns of object, nested table, varray, or REF type, Oracle Database maps the columns of the user-defined types to relational columns, in effect creating hidden columns that count toward the 1000-column limit.
datatype
Specify the datatype of a column.
Notes on Table Column Datatypes The following notes apply to the datatypes of table columns:
If you specify AS subquery, then you can omit datatype. If you are creating an index-organized table and you specify AS subquery, then you must omit the datatype.
You can also omit datatype if the statement designates the column as part of a foreign key in a referential integrity constraint. Oracle Database automatically assigns to the column the datatype of the corresponding column of the referenced key of the referential integrity constraint.
Do not create a table with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.
Restriction on Table Column Datatypes You can specify a column of type ROWID, but Oracle Database does not guarantee that the values in such columns are valid rowids.
The SORT keyword is valid only if you are creating this table as part of a hash cluster and only for columns that are also cluster columns.
This clause instructs the database to sort the rows of the cluster on this column before applying the hash function. Doing so may improve response time during subsequent operations on the clustered data.
See Also:
"CLUSTER Clause" for information on creating a cluster tableThe DEFAULT clause lets you specify a value to be assigned to the column if a subsequent INSERT statement omits a value for the column. The datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression.
The DEFAULT expression can include any SQL function as long as the function does not return a literal argument, a column reference, or a nested function invocation.
Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.
The ENCRYPT clause lets you use the transparent data encryption feature to encrypt the column you are defining. You can encrypt columns of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, DATE, and RAW. The data does not appear in its encrypted form to authorized users, such as the user who encrypts the column.
Note:
Column encryption requires that a system administrator with appropriate privileges has initialized the security module, opened a wallet, and set an encryption key. Please refer to Oracle Advanced Security Administrator's Guide for general information on encryption and to alter_system_security_clauses for relatedALTER SYSTEM statements.USING 'encrypt_algorithm' Use this clause to specify the name of the algorithm to be used. Valid algorithms are 3DES168, AES128, AES192, and AES256. If you omit this clause, the database uses AES192. If you encrypt more than one column in the same table, and if you specify the USING clause for one of the columns, you must specify the same encryption algorithm for all the encrypted columns.
IDENTIFIED BY password If you specify this clause, the database derives the column key from the specified password.
SALT | NO SALT By default the database appends a random string, called "salt", to the clear text of the column before encrypting it. If you want to use the column as an index key, you must specify NO SALT. Please refer to Oracle Advanced Security Administrator's Guide for a description of "salt" in this context.
Restrictions on the ENCRYPT clause: The following restrictions apply to column encryption:
To encrypt a column in an external table, the table must use ORACLE_DATAPUMP as its access type.
You cannot encrypt a column in tables owned by SYS.
You cannot encrypt a foreign key column.
See Also:
Oracle Advanced Security Administrator's Guide for more information about transparent data encryptionConstraint Clauses
Use these clauses to create constraints on the table columns. You must specify a PRIMARY KEY constraint for an index-organized table, and it cannot be DEFERRABLE. Please refer to constraint for syntax and description of these constraints as well as examples.
inline_ref_constraint and out_of_line_ref_constraint These clauses let you describe a column of type REF. The only difference between these clauses is that you specify out_of_line_ref_constraint from the table level, so you must identify the REF column or attribute you are defining. Specify inline_ref_constraint as part of the definition of the REF column or attribute.
See Also:
"REF Constraint Examples"inline_constraint Use the inline_constraint to define an integrity constraint as part of the column definition.
You can create UNIQUE, PRIMARY KEY, and REFERENCES constraints on scalar attributes of object type columns. You can also create NOT NULL constraints on object type columns and CHECK constraints that reference object type columns or any attribute of an object type column.
out_of_line_constraint Use the out_of_line_constraint syntax to define an integrity constraint as part of the table definition.
The supplemental_logging_props clause lets you instruct the database to put additional data into the log stream to support log-based tools.
supplemental_log_grp_clause Use this clause to create a named log group.
The NO LOG clause lets you omit from the redo log one or more columns that would otherwise be included in the redo for the named log group. You must specify at least one fixed-length column without NO LOG in the named log group.
If you specify ALWAYS, then during an update, the database includes in the redo all columns in the log group. This is called an unconditional log group (sometimes called an "always log group"), because Oracle Database supplementally logs all the columns in the log group when the associated row is modified. If you omit ALWAYS, then the database supplementally logs all the columns in the log group only if any column in the log group is modified. This is called a conditional log group.
You can query the appropriate USER_, ALL_, or DBA_LOG_GROUP_COLUMNS data dictionary view to determine whether any supplemental logging has already been specified.
supplemental_id_key_clause Use this clause to specify that all or a combination of the primary key, unique key, and foreign key columns should be supplementally logged. Oracle Database will generate either an unconditional log group or a conditional log group. With an unconditional log group, the database supplementally logs all the columns in the log group when the associated row is modified. With a conditional log group, the database supplementally logs all the columns in the log group only if any column in the log group is modified.
If you specify ALL COLUMNS, then the database includes in the redo log all the fixed-length maximum size columns of that row. Such a redo log is a system-generated unconditional log group.
If you specify PRIMARY KEY COLUMNS, then for all tables with a primary key, the database places into the redo log all columns of the primary key whenever an update is performed. Oracle Database evaluates which columns to supplementally log as follows:
First the database chooses columns of the primary key constraint, if the constraint is validated or marked RELY and is not marked as DISABLED or INITIALLY DEFERRED.
If no primary key columns exist, then the database looks for the smallest UNIQUE index with at least one NOT NULL column and uses the columns in that index.
If no such index exists, then the database supplementally logs all scalar columns of the table.
If you specify UNIQUE COLUMNS, then for all tables with a unique key or a bitmap index, if any of the unique key or bitmap index columns are modified, the database places into the redo log all other columns belonging to the unique key or bitmap index. Such a log group is a system-generated conditional log group.
If you specify FOREIGN KEY COLUMNS, then for all tables with a foreign key, if any foreign key columns are modified, the database places into the redo log all other columns belonging to the foreign key. Such a redo log is a system-generated conditional log group.
If you specify this clause multiple times, then the database creates a separate log group for each specification. You can query the appropriate USER_, ALL_, or DBA_LOG_GROUPS data dictionary view to determine whether any supplemental logging data has already been specified.
The ON COMMIT clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.
DELETE ROWS Specify DELETE ROWS for a transaction-specific temporary table. This is the default. Oracle Database will truncate the table (delete all its rows) after each commit.
PRESERVE ROWS Specify PRESERVE ROWS for a session-specific temporary table. Oracle Database will truncate the table (delete all its rows) when you terminate the session.
physical_properties
The physical properties relate to the treatment of extents and segments and to the storage characteristics of the table.
The segment_attributes_clause lets you specify physical attributes and tablespace storage for the table.
physical_attributes_clause The physical_attributes_clause lets you specify the value of the PCTFREE, PCTUSED, and INITRANS parameters and the storage characteristics of the table.
For a nonpartitioned table, each parameter and storage characteristic you specify determines the actual physical attribute of the segment associated with the table.
For partitioned tables, the value you specify for the parameter or storage characteristic is the default physical attribute of the segments associated with all partitions specified in this CREATE statement (and in subsequent ALTER TABLE ... ADD PARTITION statements), unless you explicitly override that value in the PARTITION clause of the statement that creates the partition.
If you omit this clause, then Oracle Database sets PCTFREE to 10, PCTUSED to 40, and INITRANS to 1.
See Also:
physical_attributes_clause and storage_clause for a description of these clauses
TABLESPACE Specify the tablespace in which Oracle Database creates the table, object table OIDINDEX, partition, LOB data segment, LOB index segment, or index-organized table overflow data segment. If you omit TABLESPACE, then the database creates that item in the default tablespace of the owner of the schema containing the table.
For a heap-organized table with one or more LOB columns, if you omit the TABLESPACE clause for LOB storage, then the database creates the LOB data and index segments in the tablespace where the table is created.
For an index-organized table with one or more LOB columns, if you omit TABLESPACE, then the LOB data and index segments are created in the tablespace in which the primary key index segment of the index-organized table is created.
For nonpartitioned tables, the value specified for TABLESPACE is the actual physical attribute of the segment associated with the table. For partitioned tables, the value specified for TABLESPACE is the default physical attribute of the segments associated with all partitions specified in the CREATE statement and on subsequent ALTER TABLE ... ADD PARTITION statements, unless you specify TABLESPACE in the PARTITION description.
See Also:
CREATE TABLESPACE for more information on tablespacesSpecify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING).The logging attribute of the table is independent of that of its indexes.
This attribute also specifies whether subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged (LOGGING) or not logged (NOLOGGING).
Please refer to logging_clause for a full description of this clause.
The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.
When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table:
For an entire table, in the physical_properties clause of relational_table or object_table
For a range partition, in the table_partition_description of the range_partitioning clause
For a list partition, in the table_partition_description of the list_partitioning clause
For the storage table of a nested table, in the nested_table_col_properties clause
See Also:
"Conventional and Direct-Path INSERT" for information on direct-path INSERT operations, including restrictions
Oracle Database Performance Tuning Guide for information on calculating the compression ratio and to Oracle Data Warehousing Guide for information on table compression usage scenarios
Restrictions on Table Compression Table compression is subject to the following restrictions:
Table compression is not supported for tables with more than 255 columns.
LOB data segments are not compressed.
You cannot specify table compression for an index-organized table, any overflow segment or partition of an overflow segment, or any mapping table segment of an index-organized table.
You cannot define table compression explicitly for hash partitions or hash and list subpartitions. The table compression attribute for those partitions is inherited from the tablespace, the table, or the table partition setting.
You cannot specify table compression for external tables or for tables that are part of a cluster.
These keywords are deprecated and have been replaced with LOGGING and NOLOGGING, respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle strongly recommends that you use the LOGGING and NOLOGGING keywords.
Restrictions on [UN]RECOVERABLE This clause is subject to the following restrictions:
You cannot specify RECOVERABLE for partitioned tables or LOB storage characteristics.
You cannot specify UNRECOVERABLE for partitioned or index-organized tables.
You can specify UNRECOVERABLE only with AS subquery.
The ORGANIZATION clause lets you specify the order in which the data rows of the table are stored.
HEAP HEAP indicates that the data rows of table are stored in no particular order. This is the default.
INDEX INDEX indicates that table is created as an index-organized table. In an index-organized table, the data rows are held in an index defined on the primary key for the table.
EXTERNAL EXTERNAL indicates that table is a read-only table located outside the database.
See Also:
"External Table Example"index_org_table_clause
Use the index_org_table_clause to create an index-organized table. Oracle Database maintains the table rows, both primary key column values and nonkey column values, in an index built on the primary key. Index-organized tables are therefore best suited for primary key-based access and manipulation. An index-organized table is an alternative to:
A noncluster table indexed on the primary key by using the CREATE INDEX statement
A cluster table stored in an indexed cluster that has been created using the CREATE CLUSTER statement that maps the primary key for the table to the cluster key
You must specify a primary key for an index-organized table, because the primary key uniquely identifies a row. The primary key cannot be DEFERRABLE. Use the primary key instead of the rowid for directly accessing index-organized rows.
If an index-organized table is partitioned and contains LOB columns, then you should specify the index_org_table_clause first, then the LOB_storage_clause, and then the appropriate table_partitioning_clauses.
You cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE ... AS SELECT statement if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement.
See Also:
"Index-Organized Table Example"Restrictions on Index-Organized Tables Index-organized tables are subject to the following restrictions:
You cannot specify a column of type ROWID for an index-organized table.
You cannot specify the composite_partitioning_clause for an index-organized table.
PCTTHRESHOLD integer Specify the percentage of space reserved in the index block for an index-organized table row. PCTTHRESHOLD must be large enough to hold the primary key. All trailing columns of a row, starting with the column that causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50. If you do not specify PCTTHRESHOLD, the default is 50.
Restriction on PCTTHRESHOLD You cannot specify PCTTHRESHOLD for individual partitions of an index-organized table.
mapping_table_clauses Specify MAPPING TABLE to instruct the database to create a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table. If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and physical attributes as the base table partitions.
Oracle Database creates the mapping table or mapping table partition in the same tablespace as its parent index-organized table or partition. You cannot query, perform DML operations on, or modify the storage characteristics of the mapping table or its partitions.
key_compression The key_compression clauses let you enable or disable key compression for index-organized tables.
Specify COMPRESS to enable key compression, which eliminates repeated occurrence of primary key column values in index-organized tables. Use integer to specify the prefix length, which is the number of prefix columns to compress.
The valid range of prefix length values is from 1 to the number of primary key columns minus 1. The default prefix length is the number of primary key columns minus 1.
Specify NOCOMPRESS to disable key compression in index-organized tables. This is the default.
Restriction on Key Compression of Index-organized Tables At the partition level, you can specify COMPRESS, but you cannot specify the prefix length with integer.
index_org_overflow_clause The index_org_overflow_clause lets you instruct the database that index-organized table data rows exceeding the specified threshold are placed in the data segment specified in this clause.
When you create an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified OVERFLOW, then the database raises an error and does not execute the CREATE TABLE statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.
All physical attributes and storage characteristics you specify in this clause after the OVERFLOW keyword apply only to the overflow segment of the table. Physical attributes and storage characteristics for the index-organized table itself, default values for all its partitions, and values for individual partitions must be specified before this keyword.
If the index-organized table contains one or more LOB columns, then the LOBs will be stored out-of-line unless you specify OVERFLOW, even if they would otherwise be small enough be to stored inline.
If table is partitioned, then the database equipartitions the overflow data segments with the primary key index segments.
INCLUDING column_name Specify a column at which to divide an index-organized table row into index and overflow portions. The primary key columns are always stored in the index. column_name can be either the last primary key column or any non primary key column. All non primary key columns that follow column_name are stored in the overflow data segment.
If an attempt to divide a row at column_name causes the size of the index portion of the row to exceed the specified or default PCTTHRESHOLD value, then the database breaks up the row based on the PCTTHRESHOLD value.
Restriction on the INCLUDING Clause You cannot specify this clause for individual partitions of an index-organized table.
Use the external_table_clause to create an external table, which is a read-only table whose metadata is stored in the database but whose data in stored outside the database. Among other capabilities, external tables let you query data without first loading it into the database.
See Also:
Oracle Data Warehousing Guide, Oracle Database Administrator's Guide, and Oracle Database Utilities for information on the uses for external tablesBecause external tables have no data in the database, you define them with a small subset of the clauses normally available when creating tables.
Within the relational_properties clause, you can specify only column and datatype.
Within the physical_properties_clause, you can specify only the organization of the table (ORGANIZATION EXTERNAL external_table_clause).
Within the table_properties clause, you can specify only the parallel_clause. The parallel_clause lets you parallelize subsequent queries on the external data and subsequent operations that populate the external table.
You can populate the external table at create time by using the AS subquery clause.
No other clauses are permitted in the same CREATE TABLE statement.
See Also:
ALTER TABLE ... "PROJECT COLUMN Clause" for information on the effect of changing the default property of the column projection
Restrictions on External Tables External tables are subject to the following restrictions:
An external table cannot be a temporary table.
You cannot specify constraints on an external table.
An external table cannot have object type, varray, or LONG columns. However, you can populate LOB columns of an external table with varray or LONG data from an internal database table.
TYPE TYPE access_driver_type indicates the access driver of the external table. The access driver is the API that interprets the external data for the database. Oracle Database provides two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. If you do not specify TYPE, then the database uses ORACLE_LOADER as the default access driver. You must specify the ORACLE_DATAPUMP access driver if you specify the AS subquery clause to unload data from one Oracle Database and reload it into the same or a different Oracle Database.
See Also:
Oracle Database Utilities for information about theORACLE_LOADER and ORACLE_DATAPUMP access driversDEFAULT DIRECTORY DEFAULT DIRECTORY lets you specify a default directory object corresponding to a directory on the file system where the external data sources may reside. The default directory can also be used by the access driver to store auxiliary files such as error logs.
ACCESS PARAMETERS The optional ACCESS PARAMETERS clause lets you assign values to the parameters of the specific access driver for this external table.
The opaque_format_spec lets you list the parameters and their values. Please refer to Oracle Database Utilities for information on how to specify values for the opaque_format_spec.
Field names specified in the opaque_format_spec must match columns in the table definition. Oracle Database ignores any field in the opaque_format_spec that is not matched by a column in the table definition.
USING CLOB subquery lets you derive the parameters and their values through a subquery. The subquery cannot contain any set operators or an ORDER BY clause. It must return one row containing a single item of datatype CLOB.
Whether you specify the parameters in an opaque_format_spec or derive them using a subquery, the database does not interpret anything in this clause. It is up to the access driver to interpret this information in the context of the external data.
LOCATION The LOCATION clause lets you specify one or more external data sources. Usually the location_specifier is a file, but it need not be. Oracle Database does not interpret this clause. It is up to the access driver to interpret this information in the context of the external data. You cannot use wildcards in the location_specifier to specify multiple files.
REJECT LIMIT The REJECT LIMIT clause lets you specify how many conversion errors can occur during a query of the external data before an Oracle Database error is returned and the query is aborted. The default value is 0.
The CLUSTER clause indicates that the table is to be part of cluster. The columns listed in this clause are the table columns that correspond to the cluster columns. Generally, the cluster columns of a table are the column or columns that make up its primary key or a portion of its primary key. Please refer to CREATE CLUSTER for more information.
Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name.
A cluster table uses the space allocation of the cluster. Therefore, do not use the PCTFREE, PCTUSED, or INITRANS parameters, the TABLESPACE clause, or the storage_clause with the CLUSTER clause.
Restrictions on Cluster Tables Cluster tables are subject to the following restrictions:
Object tables and tables containing LOB columns cannot be part of a cluster.
You cannot specify CLUSTER with either ROWDEPENDENCIES or NOROWDEPENDENCIES unless the cluster has been created with the same ROWDEPENDENCIES or NOROWDEPENDENCIES setting.
table_properties
The table_properties further define the characteristics of the table.
Use the column_properties clauses to specify the storage attributes of a column.
The object_type_col_properties determine storage characteristics of an object column or attribute or of an element of a collection column or attribute.
column For column, specify an object column or attribute.
substitutable_column_clause The substitutable_column_clause indicates whether object columns or attributes in the same hierarchy are substitutable for each other. You can specify that a column is of a particular type, or whether it can contain instances of its subtypes, or both.
If you specify ELEMENT, you constrain the element type of a collection column or attribute to a subtype of its declared type.
The IS OF [TYPE] (ONLY type) clause constrains the type of the object column to a subtype of its declared type.
NOT SUBSTITUTABLE AT ALL LEVELS indicates that the object column cannot hold instances corresponding to any of its subtypes. Also, substitution is disabled for any embedded object attributes and elements of embedded nested tables and varrays. The default is SUBSTITUTABLE AT ALL LEVELS.
Restrictions on the substitutable_column_clause This clause is subject to the following restrictions:
You cannot specify this clause for an attribute of an object column. However, you can specify this clause for a object type column of a relational table and for an object column of an object table if the substitutability of the object table itself has not been set.
For a collection type column, the only part of this clause you can specify is [NOT] SUBSTITUTABLE AT ALL LEVELS.
The LOB_storage_clause lets you specify the storage attributes of LOB data segments.
For a nonpartitioned table, this clause specifies the storage attributes of LOB data segments of the table.
For a partitioned table, Oracle Database implements this clause depending on where it is specified:
For a partitioned table specified at the table level--that is, when specified in the physical_properties clause along with one of the partitioning clauses--this clause specifies the default storage attributes for LOB data segments associated with each partition or subpartition. These storage attributes apply to all partitions or subpartitions unless overridden by a LOB_storage_clause at the partition or subpartition level.
For an individual partition of a partitioned table--that is, when specified as part of a table_partition_description--this clause specifies the storage attributes of the data segments of the partition or the default storage attributes of any subpartitions of the partition. A partition-level LOB_storage_clause overrides a table-level LOB_storage_clause.
For an individual subpartition of a partitioned table--that is, when specified as part of subpartition_by_hash or subpartition_by_list--this clause specifies the storage attributes of the data segments of the subpartition. A subpartition-level LOB_storage_clause overrides both partition-level and table-level LOB_storage_clauses.
See Also:
Oracle Database Application Developer's Guide - Large Objects for detailed information about LOBs, including guidelines for creating gigabyte LOBs
LOB_item
Specify the LOB column name or LOB object attribute for which you are explicitly defining tablespace and storage characteristics that are different from those of the table. Oracle Database automatically creates a system-managed index for each LOB_item you create.
LOB_segname
Specify the name of the LOB data segment. You cannot use LOB_segname if you specify more than one LOB_item.
The LOB_parameters clause lets you specify various elements of LOB storage.
ENABLE STORAGE IN ROW If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information. This is the default.
Restriction on Enabling Storage in Row For an index-organized table, you cannot specify this parameter unless you have specified an OVERFLOW segment in the index_org_table_clause.
DISABLE STORAGE IN ROW If you disable storage in row, then the LOB value is stored outside of the row out of line regardless of the length of the LOB value.
The LOB locator is always stored inline regardless of where the LOB value is stored. You cannot change the value of STORAGE IN ROW once it is set except by moving the table. See the move_table_clause in the ALTER TABLE documentation for more information.
CHUNK integer Specify the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, then the database rounds up in bytes to the next multiple. For example, if the database block size is 2048 and integer is 2050, then the database allocates 4096 bytes (2 blocks). The maximum value is 32768 (32K), which is the largest Oracle Database block size allowed. The default CHUNK size is one Oracle Database block.
The value of CHUNK must be less than or equal to the value of NEXT, either the default value or that specified in the storage_clause. If CHUNK exceeds the value of NEXT, then the database returns an error.You cannot change the value of CHUNK once it is set.
PCTVERSION integer Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode. You cannot specify both PCTVERSION and RETENTION.
RETENTION Use this clause to indicate that Oracle Database should retain old versions of this LOB column. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database.
You can specify the RETENTION parameter only if the database is running in automatic undo mode. In this mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION.
See Also:
"Creating an Undo Tablespace: Example"FREEPOOLS integer Specify the number of groups of free lists for the LOB segment. Normally integer will be the number of instances in a Real Application Clusters environment or 1 for a single-instance database.
You can specify this parameter only if the database is running in automatic undo mode. In this mode, FREEPOOLS is the default unless you specify the FREELIST GROUPS parameter of the storage_clause. If you specify neither FREEPOOLS nor FREELIST GROUPS, then the database uses a default of FREEPOOLS 1 if the database is in automatic undo management mode and a default of FREELIST GROUPS 1 if the database is in manual undo management mode.
Restriction on FREEPOOLS You cannot specify both FREEPOOLS and the FREELIST GROUPS parameter of the storage_clause.
The varray_col_properties let you specify separate storage characteristics for the LOB in which a varray will be stored. If varray_item is a multilevel collection, then the database stores all collection items nested within varray_item in the same LOB in which varray_item is stored.
For a nonpartitioned table--that is, when specified in the physical_properties clause without any of the partitioning clauses--this clause specifies the storage attributes of the LOB data segments of the varray.
For a partitioned table specified at the table level--that is, when specified in the physical_properties clause along with one of the partitioning clauses--this clause specifies the default storage attributes for the varray LOB data segments associated with each partition (or its subpartitions, if any).
For an individual partition of a partitioned table--that is, when specified as part of a table_partition_description--this clause specifies the storage attributes of the varray LOB data segments of that partition or the default storage attributes of the varray LOB data segments of any subpartitions of this partition. A partition-level varray_col_properties overrides a table-level varray_col_properties.
For an individual subpartition of a partitioned table--that is, when specified as part of subpartition_by_hash or subpartition_by_list--this clause specifies the storage attributes of the varray data segments of this subpartition. A subpartition-level varray_col_properties overrides both partition-level and table-level varray_col_properties.
STORE AS LOB Clause If you specify STORE AS LOB:
If the maximum varray size is less than 4000 bytes, then the database stores the varray as an inline LOB unless you have disabled storage in row.
If the maximum varray size is greater than 4000 bytes or if you have disabled storage in row, then the database stores in the varray as an out-of-line LOB.
If you do not specify STORE AS LOB, then storage is based on the maximum possible size of the varray rather than on the actual size of a varray column. The maximum size of the varray is the number of elements times the element size, plus a small amount for system control information. If you omit this clause:
If the maximum size of the varray is less than 4000 bytes, then the database does not store the varray as a LOB, but as inline data.
If the maximum size is greater than 4000 bytes, then the database always stores the varray as a LOB.
If the actual size is less than 4000 bytes, it is stored as an inline LOB
If the actual size is greater than 4000 bytes, it is stored as an out-of-line LOB, as is true for other LOB columns.
Restriction on Storing Varrays as LOBs You cannot specify the TABLESPACE parameter of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the tablespace of the containing table or table partition.
substitutable_column_clause The substitutable_column_clause has the same behavior as described for object_type_col_properties.
The nested_table_col_properties let you specify separate storage characteristics for a nested table, which in turn enables you to define the nested table as an index-organized table. Unless you explicitly specify otherwise in this clause:
For a nonpartitioned table, the storage table is created in the same schema and the same tablespace as the parent table.
For a partitioned table, the storage table is created in the default tablespace of the schema.
In either case, the storage table uses default storage characteristics, and stores the nested table values of the column for which it was created.
You must include this clause when creating a table with columns or column attributes whose type is a nested table. Clauses within nested_table_col_properties that function the same way they function for the parent table are not repeated here.
nested_item Specify the name of a column, or of a top-level attribute of the object type of the tables, whose type is a nested table.
COLUMN_VALUE If the nested table is a multilevel collection, then the inner nested table or varray may not have a name. In this case, specify COLUMN_VALUE in place of the nested_item name.
storage_table Specify the name of the table where the rows of nested_item reside.
You cannot query or perform DML statements on storage_table directly, but you can modify its storage characteristics by specifying its name in an ALTER TABLE statement.
Restriction on the Storage Table You cannot partition the storage table of a nested table.
See Also:
ALTER TABLE for information about modifying nested table column storage characteristicsRETURN AS Specify what Oracle Database returns as the result of a query.
VALUE returns a copy of the nested table itself.
LOCATOR returns a collection locator to the copy of the nested table.
The locator is scoped to the session and cannot be used across sessions. Unlike a LOB locator, the collection locator cannot be used to modify the collection instance.
If you do not specify the segment_attributes_clause or the LOB_storage_clause, then the nested table is heap organized and is created with default storage characteristics.
Restrictions on Nested Table Column Properties Nested table column properties are subject to the following restrictions:
You cannot specify this clause for a temporary table.
You cannot specify the oid_clause.
At create time, you cannot use object_properties to specify an out_of_line_ref_constraint, inline_ref_constraint, or foreign key constraint for the attributes of a nested table. However, you can modify a nested table to add such constraints using ALTER TABLE.
See Also:
ALTER TABLE for information about modifying nested table column storage characteristics
The XMLType_column_properties let you specify storage attributes for an XMLTYPE column.
XMLType_storage XMLType columns can be stored either in LOB or object-relational columns.
Specify STORE AS OBJECT RELATIONAL if you want the database to store the XMLType data in object-relational columns. Storing data objects relationally lets you define indexes on the relational columns and enhances query performance.
If you specify object-relational storage, then you must also specify the XMLSchema_spec clause.
Specify STORE AS CLOB if you want the database to store the XMLType data in a CLOB column. Storing data in a CLOB column preserves the original content and enhances retrieval time.
If you specify LOB storage, then you can specify either LOB parameters or the XMLSchema_spec clause, but not both. Specify the XMLSchema_spec clause if you want to restrict the table or column to particular schema-based XML instances.
XMLSchema_spec This clause lets you specify the URL of a registered XMLSchema, either in the XMLSCHEMA clause or as part of the ELEMENT clause, and an XML element name. You must specify an element, although the XMLSchema URL is optional. If you do specify an XMLSchema URL, then you must already have registered the XMLSchema using the DBMS_XMLSCHEMA package.
See Also:
LOB_storage_clause for information on the LOB_segname and LOB_parameters clauses
"XMLType Column Examples" for examples of XMLType columns in object-relational tables and "Using XML in SQL Statements" for an example of creating an XMLSchema
Oracle XML DB Developer's Guide for more information on XMLType columns and tables and on creating XMLSchemas
PL/SQL Packages and Types Reference for information on the DBMS_XMLSCHEMA package
Use the table_partitioning_clauses to create a partitioned table.
Restrictions on Partitioning in General All partitioning is subject to the following restrictions:
You cannot partition a table that is part of a cluster.
You cannot partition a table containing any LONG or LONG RAW columns.
The storage of partitioned dat