CREATE TABLE (original) (raw)

Purpose

Use the CREATE TABLE statement to create one of the following types of tables:

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:

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 specify an edition in the evaluation_edition_clause or the unusable_editions_clause, you must have the USE privilege on the edition.

To specify the zonemap_clause, you must have the permissions necessary to create a zone map. Refer to the "Prerequisites" section in the documentation on CREATE MATERIALIZED ZONEMAP.

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.

To create an XMLType table in a different database schema from your own, you must have not only privilege CREATE ANY TABLE but also privilege CREATE ANY INDEX. This is because a unique index is created on column OBJECT_ID when you create the table. Column OBJECT_ID stores a system-generated object identifier.

See Also:

Syntax

evaluation_edition_clause::=

unusable_editions_clause::=

object_table_substitution::=

deferred_segment_creation::=

table_properties::=

(column_properties::=, read_only_clause::=, indexing_clause::=, table_partitioning_clauses::=, attribute_clustering_clause::=, parallel_clause::=, enable_disable_clause::=, row_movement_clause::=, logical_replication_clause::=, flashback_archive_clause::=, subquery::=)

object_type_col_properties::=

substitutable_column_clause::=

LOB_deduplicate_clause::=

LOB_compression_clause::=

LOB_partitioning_storage::=

XMLType_virtual_columns::=

logical_replication_clause::=

flashback_archive_clause::=

advanced_index_compression::=

supplemental_logging_props::=

supplemental_log_grp_clause::=

supplemental_id_key_clause::=

immutable_table_clauses::=

immutable_table_no_drop_clause::=

immutable_table_no_delete_clause::=

blockchain_table_clauses::=

blockchain_drop_table_clause::=

blockchain_row_retention_clause::=

blockchain_hash_and_data_format_clause::=

external_table_data_props::=

(opaque_format_spec: This clause specifies the access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers. See Oracle Database Utilities for descriptions of these parameters.)

external_part_subpart_data_props::=

consistent_hash_partitions::=

consistent_hash_with_subpartitions::=

range_partitionset_clause::=

range_partitionset_desc::=

list_partitionset_clause::=

list_partitionset_desc::=

hash_subparts_by_quantity::=

table_partition_description::=

(deferred_segment_creation::=, read_only_clause::=, indexing_clause::=, segment_attributes_clause::=, table_compression::=, prefix_compression::=, inmemory_clause::=, segment_attributes_clause::=, LOB_storage_clause::=, varray_col_properties::=, nested_table_col_properties::=)

clustering_column_group::=

enable_disable_clause::=

(using_index_clause::=, exceptions_clause not supported in CREATE TABLE statements)

Semantics

GLOBAL TEMPORARY

Specify GLOBAL TEMPORARY to create a temporary table, whose 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 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 clause.

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 with an INSERT operation on the table. A session becomes unbound to a temporary table with a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.

PRIVATE TEMPORARY

Specify PRIVATE TEMPORARY to create a private temporary table.

A private temporary table differs from a temporary table in that its definition and data are visible only within the session that created it. Use the ON COMMIT clause to define the scope of a private temporary table: either transaction or session. The ON COMMIT clause used with the keywords DROP DEFINITION creates a transaction-specific table whose data and definition are dropped when the transaction commits. This is the default behavior. The ON COMMIT clause used with keywords PRESERVE DEFINITION creates a session-specific table whose definition is preserved when the transaction commits. See here for usage details of theON COMMIT clause.

Three DDL statements are supported for private temporary tables: CREATE, DROP, and TRUNCATE.

Restrictions

You must be a user other than SYS to create private temporary tables.

Restrictions on Temporary Tables

Temporary tables are subject to the following restrictions:

Restrictions on Private Temporary Tables

In addition to the general limitations of temporary tables, private temporary tables are subject to the following restrictions:

SHARDED

Specify SHARDED to create a sharded table.

This clause is valid only if you are using Oracle Sharding, which is a data tier architecture in which data is horizontally partitioned across independent databases. Each database in such configuration is called a shard. All of the shards together make up a single logical database, which is referred to as a sharded database (SDB). Horizontal partitioning involves splitting a table across shards so that each shard contains the table with the same columns but a different subset of rows. A table split up in this manner is called a sharded table.

When you create a sharded table, you must specify a tablespace set in which to create the table. There is no default tablespace set for sharded tables. See CREATE TABLESPACE SET for more information.

Oracle Sharding is based on the Oracle Partitioning feature. Therefore, a sharded table must be a partitioned or composite-partitioned table. When creating a sharded table, you must specify one of the table_partitioning_clauses. See table_partitioning_clauses for the full semantics of these clauses.

Restrictions on Sharded Tables

The following restrictions apply to sharded tables:

DUPLICATED

This clause is valid only if you are using Oracle Sharding. Specify DUPLICATED to create a duplicated table, which is duplicated on all shards. It can be a nonpartitioned table or partitioned table.

Duplicated tables are not tied to any table family.

Restrictions on Duplicated Tables

The following restrictions apply to duplicated tables:

IMMUTABLE

Specify the IMMUTABLE keyword to create a read-only table that protects data from unauthorized modification by insiders.

You can create a blockchain table that is also immutable by using the keywords IMMUTABLE BLOCKCHAIN in CREATE TABLE.

You must specify the mandatory immutable_table_clauses when you create an immutable table using the CREATE IMMUTABLE TABLE statement.

Immutable tables support VPD policies, distributed transactions, and XA transactions.

Prerequistes

BLOCKCHAIN

Specify the BLOCKCHAIN keyword to create a blockchain table.

You must specify the mandatory blockchain_table_clauses when you create a blockchain table using the CREATE BLOCKCHAIN TABLE statement.

When you create a blockchain table, an entry is created in the dictionary table blockchain_table$ owned by SYS .

Restrictions

The following CREATE TABLE clauses are disallowed with the creation of blockchain 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. The name must satisfy the requirements listed in "Database Object Naming Rules".

SHARING

This clause applies only when creating a table in an application root. This type of table is called an application common object and its data can be shared with the application PDBs that belong to the application root. To determine how the table data is shared, specify one of the following sharing attributes:

If you omit this clause, then the database uses the value of the DEFAULT_SHARING initialization parameter to determine the sharing attribute of the table. If the DEFAULT_SHARING initialization parameter does not have a value, then the default is METADATA.

When creating a relational table, you can specify METADATA, DATA, EXTENDED DATA, or NONE.

When creating an object table or an XMLTYPE table, you can specify only METADATA or NONE.

You cannot change the sharing attribute of a table after it is created.

See Also:

relational_table

This clause lets you create a relational table.

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.

Specifying column_definition with AS subquery

If you specify the AS subquery clause, and each column returned by subquery has a column name or is an expression with a specified column alias, then you can omit the column_definition clause. In this case, the names of the columns of table are the same as the names of the columns returned by subquery. The exception is creating an index-organized table, for which you must specify the column_definition clause, because you must designate a primary key column. Regardless of the table type, if you specify the column_definition clause and the AS subquery clause, then you must omit datatype from the column_definition clause.

column

Specify the name of a column of the table. The name must satisfy the requirements listed in "Database Object Naming Rules".

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. 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. A relational column that stores a user-defined type attribute inherits the collation property of the attribute. In Oracle Database 12c Release 2 (12.2), user-defined types are created using the pseudo-collation property USING_NLS_COMP and their corresponding relational columns inherit this property.

datatype

Specify the data type of a column.

In general, you must specify datatype. However, the following exceptions apply:

Restrictions on Table Column Data Types

See Also:

"Data Types" for information on LONG columns and on Oracle-supplied data types

You can specify a user-defined datatype as non-persistable when creating or altering the datatype. Instances of non-persistable types cannot persist on disk. See CREATE TYPE for more on user-defined datatypes declared as non-persistable types.

COLLATE

The COLLATE clause lets you specify a data-bound collation for the column.

For column_collation_name, specify a valid named collation or pseudo-collation. For columns of data type CLOB or NCLOB, the only allowed value for column_collation_name is the pseudo-collation USING_NLS_COMP.

If you omit this clause, then the column is assigned:

Refer to the DEFAULT COLLATION clause for more information on the default collation for a table.

You can specify the COLLATE clause only if the COMPATIBLE initialization parameter is set to 12.2 or greater, and the MAX_STRING_SIZE initialization parameter is set to EXTENDED.

SORT

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.

Table rows are hashed into buckets on cluster key columns without SORT, and then sorted in each bucket on the columns with this clause. This may improve response time during subsequent operations on the clustered data.

VISIBLE | INVISIBLE

Use this clause to specify whether column is VISIBLE or INVISIBLE. The default is VISIBLE.

INVISIBLE columns are user-specified hidden columns. To display or assign a value to an INVISIBLE column, you must specify its name explicitly. For example:

Notes on VISIBLE and INVISIBLE Columns

The following notes apply to VISIBLE and INVISIBLE columns:

Restrictions on VISIBLE and INVISIBLE Columns

The following restrictions apply to VISIBLE and INVISIBLE columns:

DEFAULT

The 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 data type of the expression must match the data type specified for the column. The column must also be large 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.

The DEFAULT expression can include the sequence pseudocolumns CURRVAL and NEXTVAL, as long as the sequence exists and you have the privileges necessary to access it. Users who perform subsequent inserts that use the DEFAULT expression must have the INSERT privilege on the table and the SELECT privilege on the sequence. If the sequence is later dropped, then subsequent INSERT statements where the DEFAULT expression is used will result in an error. If you do not fully qualify the sequence by specifying the sequence owner, for example, SCOTT.SEQ1, then Oracle Database will default the sequence owner to be the user who issues the CREATE TABLE statement. For example, if user MARY creates SCOTT.TABLE and refers to a sequence that is not fully qualified, such as SEQ2, then the column will use sequence MARY.SEQ2. Synonyms on sequences undergo a full name resolution and are stored as the fully qualified sequence in the data dictionary; this is true for public and private synonyms. For example, if user BETH adds a column referring to public or private synonym SYN1 and the synonym refers to PETER.SEQ7, then the column will store PETER.SEQ7 as the default.

Restrictions on Default Column Values

Default column values are subject to the following restrictions:

ON NULL

If you specify the ON NULL clause, then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

When you specify ON NULL, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified. If you specify an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE, then an error is raised.

Restriction on the ON NULL Clause

You cannot specify this clause for an object type column or a REF column.

identity_clause

Use this clause to specify an identity column. The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. You can use the identity_options clause to configure the sequence generator.

ALWAYS

If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned. This is the default.

BY DEFAULT

If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column. If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

identity_options

Use the identity_options clause to configure the sequence generator. The identity_options clause has the same parameters as the CREATE SEQUENCE statement. Refer to CREATE SEQUENCE for a full description of these parameters and characteristics. The exception is START WITH LIMIT VALUE, which is specific to identity_options and can only be used with ALTER TABLE MODIFY. Refer to identity_options for more information.

Note:

When you create an identity column, Oracle recommends that you specify the CACHE clause with a value higher than the default of 20 to enhance performance.

Restrictions on Identity Columns

Identity columns are subject to the following restrictions:

encryption_spec

The ENCRYPT clause lets you use the Transparent Data Encryption (TDE) feature to encrypt the column you are defining. You can encrypt columns of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, DATE, LOB, 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 keystore, and set an encryption key. Refer to Oracle Database Advanced Security Guide for general information about column encryption and to security_clauses for related ALTER SYSTEM statements.

USING 'encrypt_algorithm'

Use this clause to specify the name of the algorithm to be used. Valid algorithms are AES256, AES192, AES128 and 3DES168. If the COMPATIBLE initialization parameter is set to 12.2 or higher, then the following algorithms are also valid: ARIA128, ARIA192, ARIA256, GOST256, and SEED128. If you omit this clause, then 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, then you must specify the same encryption algorithm for all the encrypted columns.

IDENTIFIED BY password

If you specify this clause, then the database derives the column key from the specified password.

'integrity_algorithm'

Use this clause to specify the integrity algorithm to be used. Valid integrity algorithms are SHA-1 and NOMAC.

All encrypted columns in a table must use the same integrity algorithm. If you already have a table column using the SHA-1 algorithm, then you cannot use the NOMAC parameter to encrypt another column in the same table. Refer to the REKEY encryption_spec clause of ALTER TABLE to learn how to change the integrity algorithm used by all encrypted columns in a table.

SALT | NO SALT

Specify SALT to instruct the database to append a random string, called "salt," to the clear text of the column before encrypting it. This is the default.

Specify NO SALT to prevent the database from appending salt to the clear text of the column before encrypting it.

The following considerations apply when specifying SALT or NO SALT for encrypted columns:

You cannot specify SALT or NO SALT for LOB encryption.

Restrictions on encryption_spec

The following restrictions apply to column encryption:

virtual_column_definition

The virtual_column_definition clause lets you create a virtual column. A virtual column is not stored on disk. Rather, the database derives the values in a virtual column on demand by computing a set of expressions or functions. Virtual columns can be used in queries, DML, and DDL statements. They can be indexed, and you can collect statistics on them. Thus, they can be treated much as other columns. Exceptions and restrictions are listed below in "Notes on Virtual Columns" and "Restrictions on Virtual Columns".

column

For column, specify the name of the virtual column.

datatype

You can optionally specify the data type of the virtual column. If you omit datatype, then the database determines the data type of the column based on the data type of the underlying expressions. All Oracle scalar data types and XMLType are supported.

COLLATE

The COLLATE clause lets you specify a data-bound collation for the virtual column. For column_collation_name, specify a valid named collation or pseudo-collation. If you omit this clause, then the column is assigned the default collation for the table as it stands at the time the column is created, unless the column belongs to a foreign key, in which case it inherits the collation from the corresponding column of the parent key. Refer to the DEFAULT COLLATION clause for more information on the default collation for a table.

You can specify the COLLATE clause only if the COMPATIBLE initialization parameter is set to 12.2 or greater, and the MAX_STRING_SIZE initialization parameter is set to EXTENDED.

VISIBLE | INVISIBLE

Use this clause to specify whether virtual column is VISIBLE or INVISIBLE. The default is VISIBLE. For complete information, refer to "VISIBLE | INVISIBLE".

GENERATED ALWAYS

The optional keywords GENERATED ALWAYS are provided for semantic clarity. They indicate that the column is not stored on disk, but is evaluated on demand.

column_expression

The AS column_expression clause determines the content of the column. Refer to "Column Expressions" for more information on column_expression.

VIRTUAL

The optional keyword VIRTUAL is provided for semantic clarity.

evaluation_edition_clause

You must specify this clause if column_expression refers to an editioned PL/SQL function. Use this clause to specify the edition that is searched during name resolution of the editioned PL/SQL function—the evaluation edition.

If you omit the evaluation_edition_clause, then editioned objects are invisible during name resolution and an error will result. If the evaluation edition is dropped, then a subsequent query on the virtual column will result in an error.

The database does not maintain dependencies on the functions referenced by a virtual column. Therefore, if a virtual column refers to a noneditioned function, and the function becomes editioned, then the following operations may raise an error:

unusable_editions_clause

This clause lets you specify that the virtual column expression is unusable for evaluating queries in one or more editions. The remaining editions form a range of editions in which it is safe for the optimizer to use the virtual column expression to evaluate queries.

For example, suppose you define a function-based index on the virtual column. The optimizer can use the function-based index to evaluate queries that contain the virtual column expression in their WHERE clause. If a query is compiled in an edition that is in the usable range of editions for the virtual column, then the optimizer will consider using the index to evaluate the query. If a query is compiled in an edition outside the usable range of editions for the virtual column, then the optimizer will not consider using the index.

UNUSABLE BEFORE Clause

This clause lets you specify that the virtual column expression is unusable for evaluating queries in the ancestors of an edition.

UNUSABLE BEGINNING WITH Clause

This clause lets you specify that the virtual column expression is unusable for evaluating queries in an edition and its descendants.

If an edition specified in this clause is subsequently dropped, there is no effect on the virtual column.

Notes on Virtual Columns

Restrictions on Virtual Columns

period_definition

Use the period_definition clause to create a valid time dimension for table.

This clause implements Temporal Validity support for table. If you specify this clause, then one column in table, the start time column, contains a start date or timestamp, and another column in table, the end time column, contains an end date or timestamp. These two columns define a valid time dimension for table—that is, a period of time for which each row is considered valid. You can use Oracle Flashback Query to retrieve rows from table based on whether they are considered valid as of a specified time, before a specified time, or during a specified time period.

You can specify at most one valid time dimension when you create a table. You can subsequently add additional valid time dimensions to a table with the add_period_clause of ALTER TABLE.

valid_time_column

Specify the name of the valid time dimension. The name must satisfy the requirements listed in "Database Object Naming Rules". Oracle Database creates an INVISIBLE virtual column with this name of data type NUMBER in table.

start_time_column and end_time_column

You can optionally specify these clauses as follows:

The names you specify for start_time_column and end_time_column must satisfy the requirements listed in "Database Object Naming Rules".

If you specify these clauses, then you must define start_time_column and end_time_column in the column_definition clause of CREATE TABLE. Each column must be of a datetime data type (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE) and can be VISIBLE or INVISIBLE.

If you do not specify these clauses, then Oracle Database creates a start time column named valid_time_column _START, and an end time column named valid_time_column _END. These columns are of data type TIMESTAMP WITH TIME ZONE and are INVISIBLE.

You can insert and update values in the start time column and end time column as you would any column, with the following considerations:

Restrictions on Valid Time Dimension Columns

The following restrictions apply to valid time dimension columns:

See Also:

Constraint 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. 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.

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.

supplemental_logging_props

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.

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 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.

immutable_table_clauses

You must specify this clause when you create an immutable table.

Example: Create an Immutable Table

The following example creates an immutable table named trade_ledger in your user schema. The immutable table can be dropped only after 40 days of inactivity. Rows cannot be deleted until 100 days after they have been inserted. 

CREATE IMMUTABLE TABLE trade_ledger (tr_id NUMBER, user_name VARCHAR2(40), tr_value NUMBER)

       NO DROP UNTIL 40 DAYS IDLE   

       NO DELETE UNTIL 100 DAYS AFTER INSERT;

blockchain_table_clauses

When you create a blockchain table, you must specify the blockchain_table_clauses :

blockchain_drop_table_clause

NO DROP [ UNTIL integer DAYS IDLE ]

Use integer to specify the number of days that the blockchain table must be idle (i.e. have no rows inserted). The minimum idle retention period is 0 days, but a recommended idle retention period is 16 days.

You can specify this clause in two ways:

blockchain_row_retention_clause

NO DELETE [ LOCKED ] | NO DELETE UNTIL integer DAYS AFTER INSERT [LOCKED]

blockchain_hash_and data_format_clause

HASHING USING sha2_512 VERSION v1

You must specify this clause last after blockchain_drop_table_clause and blockchain_row_retention_clause when you create a blockchain table.

You cannot specify this clause to modify a blockchain table using the ALTER TABLE statement.

DEFAULT COLLATION

This clause lets you specify the default collation for the table. The default collation is assigned to columns of the table that are of a character data type and are created with this statement or subsequently added to the table with an ALTER TABLE statement. For collation_name, specify a valid named collation or pseudo-collation.

If you omit this clause, then the default collation for the table is set to the effective schema default collation of the schema containing the table. Refer to the DEFAULT_COLLATION clause of ALTER SESSION for more information on the effective schema default collation.

You can override the table’s default collation and assign a data-bound collation to a particular column by specifying the COLLATE clause in the column_definition or virtual_column_definition clause of CREATE TABLE or ALTER TABLE, or the modify_col_properties or modify_virtcol_properties clause of ALTER TABLE.

You can specify the DEFAULT COLLATION clause only if the COMPATIBLE initialization parameter is set to 12.2 or greater, and the MAX_STRING_SIZE initialization parameter is set to EXTENDED.

Restriction on Collation for CLOB and NCLOB Columns

If a column has the data type of CLOB or NCLOB, then its specified collation must be USING_NLS_COMP. The collation of CLOB and NCLOB columns is always USING_NLS_COMP and is not affected by the default collation for the table.

ON COMMIT

The ON COMMIT clause is relevant only if you are creating a global 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.

The scope of a private temporary table is also defined using the ON COMMIT clause but with the keywords DROP DROP DEFINITION and PRESERVE DEFINITION to define a transaction-specific or session-specific table respectively.

DROP DEFINITION

Specify DROP DEFINITION to create a private temporary table whose content and definition are dropped when the transaction commits. The creation of a transaction-specific private temporary table does not issue an implicit commit, but can be issued within an ongoing transaction. The scope of this private temporary table is limited to the transaction. This is the default.

PRESERVE DEFINITION

Specify PRESERVE DEFINITION to create a private temporary table whose definition is preserved when the transaction commits. The creation of a session-specific private temporary table issues an implicit commit. The scope of this private temporary table is extended to the session.

physical_properties

The physical properties relate to the treatment of extents and segments and to the storage characteristics of the table.

INTERNAL | EXTERNAL

Use the keyword INTERNAL to indicate an internal partition. This is the default. Use the keyword EXTERNAL to indicate an external partition.

deferred_segment_creation

Use this clause to determine when the database should create the segment(s) for this table:

Immediate segment creation is useful, for example, if your application depends upon the object appearing in the DBA_, USER_, and ALL_SEGMENTS data dictionary views, because the object will not appear in those views until the segment is created. This clause overrides the setting of the DEFERRED_SEGMENT_CREATION initialization parameter.

To determine whether a segment has been created for an existing table or its LOB columns or indexes, query the SEGMENT_CREATED column of USER_TABLES, USER_INDEXES, or USER_LOBS.

Notes on Tables Without Segments

The following rules apply to a table whose segment has not yet been materialized:

Restrictions on Deferred Segment Creation

This clause is subject to the following restrictions:

segment_attributes_clause

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.

If you omit this clause, then Oracle Database sets PCTFREE to 10, PCTUSED to 40, and INITRANS to 1.

See Also:

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.

TABLESPACE SET

This clause is valid only when creating a sharded table by specifying the SHARDED keyword of CREATE TABLE. Use this clause to specify the tablespace set in which Oracle Database creates the table.

You can only associate a tablespace set with one table family when you use the CREATE SHARDED TABLE statement. If you try to use a tablespace set with more than one table family, an error will be thrown .

logging_clause

Specify 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).

Refer to logging_clause for a full description of this clause.

table_compression

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. The COMPRESS clauses enable table compression. The NOCOMPRESS clause disables table compression. The default is NOCOMPRESS.

COMPRESS

Specifying only the keyword COMPRESS is equivalent to specifying ROW STORE COMPRESS BASIC and enables basic table compression.

ROW STORE COMPRESS BASIC

When you enable table compression by specifying either ROW STORE COMPRESS or ROW STORE COMPRESS BASIC, you enable basic 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.

Tables with basic table compression use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause.

In earlier releases, basic table compression was enabled using COMPRESS BASIC. This syntax is still supported for backward compatibility.

ROW STORE COMPRESS ADVANCED

When you enable table compression by specifying ROW STORE COMPRESS ADVANCED, you enable Advanced Row Compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.

Tables with ROW STORE COMPRESS ADVANCED or NOCOMPRESS use the PCTFREE default value of 10, to maximize compress while still allowing for some future DML changes to the data, unless you override this default explicitly.

In earlier releases, Advanced Row Compression was called OLTP table compression and was enabled using COMPRESS FOR OLTP. This syntax is still supported for backward compatibility.

COLUMN STORE COMPRESS FOR { QUERY | ARCHIVE }

When you specify COLUMN STORE COMPRESS FOR QUERY or COLUMN STORE COMPRESS FOR ARCHIVE, you enable Hybrid Columnar Compression. With Hybrid Columnar Compression, data can be compressed during direct-path inserts, conventional inserts, and array inserts. During the load process, data is transformed into a column-oriented format and then compressed. Oracle Database uses a compression algorithm appropriate for the level you specify. In general, the higher the level, the greater the compression ratio. Hybrid Columnar Compression can result in higher compression ratios, at a greater CPU cost. Therefore, this form of compression is recommended for data that is not frequently updated.

COLUMN STORE COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.

COLUMN STORE COMPRESS FOR ARCHIVE uses higher compression ratios than COLUMN STORE COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.

Specifying COLUMN STORE COMPRESS is equivalent to specifying COLUMN STORE COMPRESS FOR QUERY HIGH.

Tables with COLUMN STORE COMPRESS FOR QUERY or COLUMN STORE COMPRESS FOR ARCHIVE use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause. For these tables, PCTFREE has no effect for blocks loaded using direct-path INSERT. PCTFREE is honored for blocks loaded using conventional INSERT, and for blocks created as a result of DML operations on blocks originally loaded using direct-path INSERT.

[NO] ROW LEVEL LOCKING

If you specify ROW LEVEL LOCKING, then Oracle Database uses row-level locking during DML operations. This improves the performance of these operations when accessing Hybrid Columnar Compressed data. If you specify NO ROW LEVEL LOCKING, then row-level locking is not used. The default is NO ROW LEVEL LOCKING.

In earlier releases, Hybrid Columnar Compression was enabled using COMPRESS FOR QUERY and COMPRESS FOR ARCHIVE. This syntax is still supported for backward compatibility.

See Also:

Oracle Database Concepts for more information on Hybrid Columnar Compression, which is a feature of certain Oracle storage systems

Notes on Table Compression

You can specify table compression for the following portions of a heap-organized table:

Restrictions on Table Compression

Table compression is subject to the following restrictions:

inmemory_table_clause

Use this clause to enable or disable the table for the In-Memory Column Store (IM column store). The IM column store is an optional, static SGA pool that stores copies of tables and partitions in a special columnar format optimized for rapid scans. The IM column store does not replace the buffer cache, but acts as a supplement so that both memory areas can store the same data in different formats.

If you omit this clause, then the table is assigned the default IM column store settings for the tablespace in which it is created. Refer to the inmemory_clause of CREATE TABLESPACE for more information on specifying the default IM column store settings for a tablespace.

In an Oracle Active Data Guard environment, if you specify this clause for a table on the primary database, then the table is enabled or disabled for the IM column store in the Oracle Active Data Guard instance.

Note:

The INMEMORY_CLAUSE_DEFAULT initialization parameter enables you to specify a default IM column store clause for new tables and materialized views. Refer to Oracle Database Reference for more information on the INMEMORY_CLAUSE_DEFAULT initialization parameter.

Restrictions on the In-Memory Column Store

The following restrictions apply to the In-Memory Column Store:

inmemory_attributes

Use the inmemory_memcompress, inmemory_priority, inmemory_distribute, and inmemory_duplicate clauses to specify how table data is stored in the IM column store.

Specify the inmemory_spatial clause to apply inmemory attributes to spatial columns of type SDO_GEOMETRY.

inmemory_memcompress

Use this clause to specify the compression method for table data stored in the IM column store. This data is called In-Memory data.

To instruct the database to not compress In-Memory data, specify NO MEMCOMPRESS.

Specify MEMCOMPRESS AUTO to instruct the database to manage the segment including actions like evict, recompress, and populate.

To instruct the database to compress In-Memory data, specify MEMCOMPRESS FOR followed by one of the following methods:

Any memcompress level can be specified via DDL, but will be ignored during population. All In-Memory Compression Units (IMCUs) will be populated as QUERY LOW transparently.

inmemory_priority

Use the PRIORITY clause to specify the data population priority for table data in the IM column store. This clause controls the priority of population, but not the speed of population.

inmemory_distribute

The DISTRIBUTE clause is applicable only if you are using Oracle Real Application Clusters (Oracle RAC) or Oracle Active Data Guard. It lets you specify how table data in the IM column store is distributed across Oracle RAC instances, and lets you specify the database instances in which the data is eligible to be populated.

AUTO and BY

Use the AUTO and BY clauses to specify how table data in the IM column store is distributed across Oracle RAC instances. You can specify the following options:

You can only use AUTO and BY to distribute the In-Memory Compression Units (IMCUs) for an object between instances in a single Oracle RAC database, not between a primary instance and standby instance in Active Data Guard.

FOR SERVICE

Use the FOR SERVICE clause to specify the Oracle RAC or Oracle Active Data Guard instances in which the object is eligible to be populated. You can specify the following options:

In Oracle RAC, the FOR SERVICE clause specifies the instances within the Oracle RAC database. In Active Data Guard, the primary and standby databases may use a single-instance or Oracle RAC configuration. In Active Data Guard, the FOR SERVICE clause specifies instances in the primary database, instances in the standby database, or a mixture of primary and standby instances.

inmemory_duplicate

The DUPLICATE clause is applicable only if you are using Oracle Real Application Clusters (Oracle RAC) on an engineered system. It controls how table data in the IM column store is duplicated across Oracle RAC instances. You can specify the following options:

inmemory_column_clause

Use this clause to enable or disable specific table columns for the IM column store, and to specify the data compression method for specific columns. If you specify this clause when creating a NO INMEMORY table, then the column settings will take effect when the table or partition is subsequently enabled for the IM column store.

If you omit the inmemory_column_clause, then all table columns use the IM column store settings for the table.

Restrictions on inmemory_column_clause

inmemory_clause

Use this clause to enable or disable a table partition for the IM column store. In order to specify this clause, the table must be enabled for the IM column store. If you omit this clause, then the table partition uses the IM column store settings for the table.

The inmemory_attributes clause has the same semantics for table partitions as for tables. Refer to the inmemory_attributes clause for full information.

INMEMORY TEXT

Specify INMEMORY TEXT clause to enable IM full text columns. The PRIORITY clause has the same effect on population of IM full text columns as standard In-Memory columns. The default priority is NONE.

The MEMCOMPRESS clause is not valid with INMEMORY TEXT.

Examples

CREATE TABLE mydoc(id NUMBER, docCreationTime DATE, doc CLOB, json_doc JSON) INMEMORY TEXT(DOC, JSON_DOC)

CREATE TABLE mydoc(id NUMBER, docCreationTime DATE, doc CLOB, json_doc JSON) INMEMORY PRIORITY CRITICAL INMEMORY TEXT(DOC, JSON_DOC)

You can apply the IMEMORY TEXT clause to search non-scalar columns in an In-Memory table. This clause enables fast In-Memory searching of text, XML, or JSON documents using the CONTAINS () or JSON_TEXTCONTAINS() operators.

INMEMORY TEXT ( column_name1, column_name2 ) specifies the list of columns to be enabled as IM full text. The columns must be of type CHAR, VARCHAR2, CLOB, BLOB, or JSON. JSON columns have JSON_TEXTCONTAINS() automatically enabled.

INMEMORY TEXT ( column_name1 USING policy1, column_name2 USING policy2 )specifies the list of columns to be enabled as IM full text along with custom indexing policies. The columns must be of type CHAR, VARCHAR2, CLOB, or BLOB. You cannot use this clause with columns of type JSON.

You can use the IMEMORY PRIORITY clause to set the order in which objects are populated.

You can specify INMEMORY on non-partitioned tables using the ORACLE_HIVE, ORACLE_HDFS, and ORACLE_BIGDATA driver types.

ilm_clause

Use this clause to add an Automatic Data Optimization policy to table.

This clause has the same semantics in CREATE TABLE and ALTER TABLE, with the following additional restriction: You can specify only the ADD POLICY clause for CREATE TABLE. Refer to the ilm_clause for the full semantics of this clause.

Restrictions on Automatic Data Optimization

Automatic Data Optimization is subject to the following restrictions:

ilm_policy_clause

Use this clause to describe the Automatic Data Optimization policy.

This clause has the same semantics in CREATE TABLE and ALTER TABLE. Refer to ilm_policy_clause for the full semantics of this clause.

RECOVERABLE | UNRECOVERABLE

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:

ORGANIZATION

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.

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:

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.

The ROWID pseudocolumn of an index-organized table returns logical rowids instead of physical rowids. A column that you create with the data type ROWID cannot store the logical rowids of the IOT. The only data you can store in a column of type ROWID is rowids from heap-organized tables. If you want to store the logical rowids of an IOT, then create a column of type UROWID instead. A column of type UROWID can store both physical and logical rowids.

Restrictions on Index-Organized Tables

Index-organized tables are subject to the following restrictions:

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, then 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.

prefix_compression

The prefix_compression clauses let you enable or disable prefix compression for index-organized tables.

Restriction on Prefix 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.

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.

EXTERNAL PARTITION ATTRIBUTES

Use the EXTERNAL PARTITION ATTRIBUTES clause to specify table level external parameters in a hybrid partitioned table.

external_table_clause

Use the external_table_clause to create an external table, which allows you to process data that is stored outside the database from within the database without loading any of the data into the database.

Defining an external table only creates metadata in the data dictionary, pointing to data outside the database and providing seamless read only access to such data.

Because external tables have no data in the database, you define them with a small subset of the clauses normally available when creating tables.

In addition to supporting external data residing in operating file systems and Big Data sources and formats such as HDFS and Hive, Oracle supports external data residing in objects via the DBMS_CLOUD package.

You can work with data in object stores using the DBMS_CLOUD package or by manually defining external tables. Oracle strongly recommends using DBMS_CLOUD for the additional functionality that is fully compatible with Oracle autonomous database.

No other clauses are permitted in the same CREATE TABLE statement.

See Also:

Restrictions on External Tables

External tables are subject to the following restrictions:

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 the following access drivers: ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE. 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.

DEFAULT 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.

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.

For inline external tables and external modify query statements you must use opaque_format_spec within single quotes. For DDL statements you must use opaque_format_spec without single quotes.

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 must specify the LOCATION clause as follows:

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.

CLUSTER Clause

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. 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:

table_properties

The table_properties further define the characteristics of the table.

column_properties

Use the column_properties clauses to specify the storage attributes of a column.

object_type_col_properties

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.

Restrictions on the substitutable_column_clause

This clause is subject to the following restrictions:

LOB_storage_clause

The LOB_storage_clause lets you specify the storage attributes of LOB data segments. You must specify at least one clause after the STORE AS keywords. If you specify more than one clause, then you must specify them in the order shown in the syntax diagram, from top to bottom.

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:

Restriction on the LOB_storage_clause:

Only the TABLESPACE clause is allowed when specifying the LOB_storage_clause in a subpartition.

See Also:

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.

SECUREFILE | BASICFILE

Use this clause to specify the type of LOB storage, either high-performance LOB (SecureFiles), or the traditional LOB (BasicFiles).

Note:

You cannot convert a LOB from one type of storage to the other. Instead you must migrate to SecureFiles or BasicFiles by using online redefinition or partition exchange.

LOB_segname

Specify the name of the LOB data segment. You cannot use LOB_segname if you specify more than one LOB_item.

LOB_storage_parameters

The LOB_storage_parameters clause lets you specify various elements of LOB storage.

TABLESPACE Clause

Use this clause to specify the tablespace in which LOB data is to be stored.

TABLESPACE SET Clause

This clause is valid only when creating a sharded table by specifying the SHARDED keyword of CREATE TABLE. Use this clause to specify the tablespace set in which LOB data is to be stored.

storage_clause

Use the storage_clause to specify various aspects of LOB segment storage. Of particular interest in the context of LOB storage is the MAXSIZE clause of the storage_clause, which can be used in combination with the LOB_retention_clause of LOB_parameters. Refer to storage_clause for more information.

LOB_parameters

Several of the LOB_parameters are no longer needed if you are using SecureFiles for LOB storage. The PCTVERSION and FREEPOOLS parameters are valid and useful only if you are using BasicFiles 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. If the database is running in manual undo mode, then 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.

This clause is not valid if you have specified SECUREFILE. If you specify both SECUREFILE and PCTVERSION, then the database silently ignores the PCTVERSION parameter.

LOB_retention_clause

Use this clause to specify whether you want the LOB segment retained for flashback purposes, consistent-read purposes, both, or neither.

You can specify the RETENTION parameter only if the database is running in automatic undo mode. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. In automatic undo mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION.

You can specify the optional settings after RETENTION only if you are using SecureFiles. The SECUREFILE parameter of the LOB_storage_clause indicates that the database will use SecureFiles to manage storage dynamically, taking into account factors such as the undo mode of the database.

If you do not specify the RETENTION parameter, or you specify RETENTION with no optional settings, then RETENTION is set to DEFAULT, which is functionally equivalent to AUTO.

See Also:

FREEPOOLS integer

Specify the number of groups of free lists for the LOB segment. Normally integer will be the number of instances in an Oracle 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.

This clause is not valid if you have specified SECUREFILE. If you specify both SECUREFILE and FREEPOOLS, then the database silently ignores the FREEPOOLS parameter.

Restriction on FREEPOOLS

You cannot specify both FREEPOOLS and the FREELIST GROUPS parameter of the storage_clause.

LOB_deduplicate_clause

This clause is valid only for SecureFiles LOBs. Use the LOB_deduplicate_clause to enable or disable LOB deduplication, which is the elimination of duplicate LOB data.

The DEDUPLICATE keyword instructs the database to eliminate duplicate copies of LOBs. Using a secure hash index to detect duplication, the database coalesces LOBs with identical content into a single copy, reducing storage consumption and simplifying storage management.

If you omit this clause, then LOB deduplication is disabled by default.

This clause implements LOB deduplication for the entire LOB segment. To enable or disable deduplication for an individual LOB, use the DBMS_LOB.SETOPTIONS procedure.

LOB_compression_clause

This clause is valid only for SecureFiles LOBs, not for BasicFiles LOBs. Use the LOB_compression_clause to instruct the database to enable or disable server-side LOB compression. Random read/write access is possible on server-side compressed LOB segments. LOB compression is independent from table compression or index compression. If you omit this clause, then the default is NOCOMPRESS.

You can specify HIGH, MEDIUM, or LOW to vary the degree of compression. The HIGH degree of compression incurs higher latency than MEDIUM but provides better compression. The LOW degree results in significantly higher decompression and compression speeds, at the cost of slightly lower compression ratio than either HIGH or MEDIUM. If you omit this optional parameter, then the default is MEDIUM.

This clause implements server-side LOB compression for the entire LOB segment. To enable or disable compression on an individual LOB, use the DBMS_LOB.SETOPTIONS procedure.

ENCRYPT | DECRYPT

These clauses are valid only for LOBs that are using SecureFiles for LOB storage. Specify ENCRYPT to encrypt all LOBs in the column. Specify DECRYPT to keep the LOB in cleartext. If you omit this clause, then DECRYPT is the default.

Refer to encryption_spec for general information on that clause. When applied to a LOB column, encryption_spec is specific to the individual LOB column, so the encryption algorithm can differ from that of other LOB columns and other non-LOB columns. Use the encryption_spec as part of the column_definition to encrypt the entire LOB column. Use the encryption_spec as part of the LOB_storage_clause in the table_partition_description to encrypt a LOB partition.

Restriction on encryption_spec for LOBs

You cannot specify the SALT or NO SALT clauses of encryption_spec for LOB encryption.

CACHE | NOCACHE | CACHE READS

Refer to CACHE | NOCACHE | CACHE READS for information on these clauses.

LOB_partition_storage

The LOB_partition_storage clause lets you specify a separate LOB_storage_clause or varray_col_properties clause for each partition. You must specify the partitions in the order of partition position. You can find the order of the partitions by querying the PARTITION_NAME and PARTITION_POSITION columns of the USER_IND_PARTITIONS view.

If you do not specify a LOB_storage_clause or varray_col_properties clause for a particular partition, then the storage characteristics are those specified for the LOB item at the table level. If you also did not specify any storage characteristics for the LOB item at the table level, then Oracle Database stores the LOB data partition in the same tablespace as the table partition to which it corresponds.

Restrictions on LOB_partition_storage

LOB_partition_storage is subject to the following restrictions:

varray_col_properties

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.

STORE AS [SECUREFILE | BASICFILE] LOB Clause

If you specify STORE AS LOB, then:

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, then:

substitutable_column_clause

The substitutable_column_clause has the same behavior as described for object_type_col_properties.

Restriction on Varray Column Properties

You cannot specify this clause on an interval partitioned table.

nested_table_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:

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.

LOCAL | GLOBAL

Specify LOCAL to equipartition the nested table with the base table. This is the default. Oracle Database automatically creates a local partitioned index for the partitioned nested table.

Specify GLOBAL to indicate that the nested table is a nonpartitioned nested table of a partitioned base table.

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.

See Also:

ALTER TABLE for information about modifying nested table column storage characteristics

RETURN [AS]

Specify what Oracle Database returns as the result of a query.

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:

See Also:

XMLType_column_properties

The XMLType_column_properties let you specify storage attributes for an XMLTYPE column.

XMLType_storage

XMLType data can be stored in binary XML, CLOB, or object-relational columns.

Use the ALL VARRAYS AS clause if you want the database to store all varrays in an XMLType column.

In earlier releases, XMLType data is stored in a CLOB column in a BasicFiles LOB by default. Beginning with Oracle Database 11g Release 2 (11.2.0.2), if the COMPATIBLE initialization parameter is 11.2 or higher and you do not specify the XMLType_storage clause, then XMLType data is stored in a binary XML column in a SecureFiles LOB. If SecureFiles LOB storage is not possible, then it is stored in a binary XML column in a BasicFiles LOB.

XMLType_virtual_columns

This clause is valid only for XMLType tables with binary XML storage, which you designate in the XMLType_storage clause. Specify the VIRTUAL COLUMNS clause to define virtual columns, which can be used as in a function-based index or in the definition of a constraint. You cannot define a constraint on such a virtual column during creation of the table, but you can use a subsequent ALTER TABLE statement to add a constraint to the column.

json_storage_clause

With support for JSON data type you can define a column of JSON data type using the JSON_storage_clause.

Creating a Table with a JSON Type Column: Example

This example creates table j_purchaseorder with JSON data type column po_document. Oracle recommends that you store JSON data as JSON type.

CREATE TABLE j_purchaseorder (id VARCHAR2 (32) NOT NULL PRIMARY KEY, date_loaded TIMESTAMP (6) WITH TIME ZONE, po_document JSON );

read_only_clause

This clause lets you specify whether to create a table, partition, or subpartition in read-only or read/write mode.

When you specify this clause for a partitioned table, you specify the default read-only or read/write mode for the table. This mode is assigned to all partitions in the table at creation time, as well as any partitions that are subsequently added to the table, unless you override this behavior by specifying the mode at the partition level.

When you specify this clause for a composite-partitioned table, you specify the default read-only or read/write mode for all partitions in the table. You can override this behavior by specifying this clause for a particular partition. The default mode of a partition is assigned to all subpartitions in the partition at creation time, as well as any subpartitions that are subsequently added to the partition, unless you override this behavior by specifying the mode at the subpartition level.

indexing_clause

The indexing_clause is valid only for partitioned tables. Use this clause to set the indexing property for a table, table partition, or table subpartition.

The indexing property determines whether table partitions and subpartitions are included in partial indexes on the table.

You can specify the indexing_clause at the table, partition, or subpartition level. When you specify the indexing_clause at the table level, in the table_properties clause, you set the default indexing property for the table. Interval partitions, which are automatically created by the database, always inherit the default indexing property for the table. Other types of partitions and subpartitions inherit the default indexing property as follows:

See Also:

Oracle Database Reference for information on viewing the indexing property of a table, table partition, or table subpartition.

Restrictions on the indexing_clause

The indexing_clause is subject to the following restrictions:

table_partitioning_clauses

Use the table_partitioning_clauses to create a partitioned table.

Notes on Partitioning in General

The following notes pertain to all types of partitioning:

Restrictions on Partitioning in General

All partitioning is subject to the following restrictions:

Restrictions on Hybrid Partitioned Tables

Hybrid partitioned tables are subject to the following restrictions:

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Refer to Oracle Database VLDB and Partitioning Guide for a discussion of these restrictions.

range_partitions

Use the range_partitions clause to partition the table on ranges of values from the column list. For an index-organized table, the column list must be a subset of the primary key columns of the table.

Restrictions on Range Partitioning

Range partitioning is subject to the restrictions listed in "Restrictions on Partitioning in General". The following additional restrictions apply:

column

Specify an ordered list of columns used to determine into which partition a row belongs. These columns are the partitioning key. You can specify virtual columns and INVISIBLE columns as partitioning key columns.

INTERVAL Clause

Use this clause to establish interval partitioning for the table. Interval partitions are partitions based on a numeric range or datetime interval. They extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions. For each automatically created partition, the database generates a name of the form SYS_P n. The database guarantees that automatically generated partition names are unique and do not violate namespace rules.

Restrictions on Interval Partitioning

The INTERVAL clause is subject to the restrictions listed in "Restrictions on Partitioning in General" and "Restrictions on Range Partitioning". The following additional restrictions apply:

PARTITION partition

If you specify a partition name, then the name partition must conform to the rules for naming schema objects and their part as described in "Database Object Naming Rules". If you omit partition, then the database generates a name as described in "Notes on Partitioning in General".

range_values_clause

Specify the noninclusive upper bound for the current partition. The value list is an ordered list of literal values corresponding to the column list in the range_partitions clause. You can substitute the keyword MAXVALUE for any literal in the value list. MAXVALUE specifies a maximum value that will always sort higher than any other value, including null.

Specifying a value other than MAXVALUE for the highest partition bound imposes an implicit integrity constraint on the table.

Note:

If table is partitioned on a DATE column, and if the date format does not specify the first two digits of the year, then you must use the TO_DATE function with the YYYY 4-character format mask for the year. The RRRR format mask is not supported in this clause. The date format is determined implicitly by NLS_TERRITORY or explicitly by NLS_DATE_FORMAT. Refer to Oracle Database Globalization Support Guide for more information on these initialization parameters.

table_partition_description

Use the table_partition_description to define the physical and storage characteristics of the table.

The clauses deferred_segment_creation, segment_attributes_clause, table_compression, inmemory_clause, and ilm_clause have the same function as described for the physical_properties of the table as a whole.

Use the indexing_clause to set the indexing property for a range, list, system, or reference table partition. Refer to the indexing_clause for more information.

The prefix_compression clause and OVERFLOW clause, have the same function as described for the index_org_table_clause.

LOB_storage_clause

The LOB_storage_clause lets you specify LOB storage characteristics for one or more LOB items in this partition or in any range or list subpartitions of this partition. If you do not specify the LOB_storage_clause for a LOB item, then the database generates a name for each LOB data partition as described in "Notes on Partitioning in General".

varray_col_properties

The varray_col_properties let you specify storage characteristics for one or more varray items in this partition or in any range or list subpartitions of this partition.

nested_table_col_properties

The nested_table_col_properties let you specify storage characteristics for one or more nested table storage table items in this partition or in any range or list subpartitions of this partition. Storage characteristics specified in this clause override any storage attributes specified at the table level.

partitioning_storage_clause

Use the partitioning_storage_clause to specify storage characteristics for hash partitions and for range, hash, and list subpartitions.

Restrictions on partitioning_storage_clause

This clause is subject to the following restrictions:

list_partitions

Use the list_partitions clause to partition the table on a list of literal values for each column in the column list. List partitioning is useful for controlling how individual rows map to specific partitions.

Restrictions on List Partitioning

List partitioning is subject to the restrictions listed in "Restrictions on Partitioning in General". The following additional restrictions apply:

AUTOMATIC

Specify AUTOMATIC to create an automatic list-partitioned table. This type of table enables the database to create additional partitions on demand.

When you create an automatic list-partitioned table, you specify partitions and partitioning key values just as you would when creating a regular list-partitioned table. However, you do not specify a DEFAULT partition. As data is loaded into the table, the database automatically creates a new partition when the loaded partitioning key values do not correspond to any of the existing partitions. If list partitioning is defined with a single partitioning key value, then the database creates a new partition for each new partitioning key value. If list partitioning is defined with multiple partitioning key columns, then the database creates a new partition for each new and unique set of partitioning key values. For each automatically created partition, the database generates a name of the form SYS_P n. The database guarantees that automatically generated partition names are unique and do not violate namespace rules.

You can specify the AUTOMATIC keyword for list-partitioned tables, and list-range, list-list, list-hash, and list-interval composite-partitioned tables. For composite-partitioned tables, each automatically created list partition will have one subpartition, unless a subpartition template is defined for the table.

If a local partitioned index is defined on an automatic list-partitioned table, then local index partitions will be created when the corresponding table partitions are created.

Restrictions on Automatic List Partitioning

Automatic list partitioning is subject to the restrictions listed in "Restrictions on List Partitioning". The following additional restrictions apply:

STORE IN

The optional STORE IN clause lets you specify one or more tablespaces into which the database will store data for the automatically created list partitions.

Note:

You can change an automatic list-partitioned table to a regular list-partitioned table, and vice versa. You can also change the tablespaces into which the database will store data for automatically created list partitions. See the clause alter_automatic_partitioning of ALTER TABLE for more information.

list_values_clause

The list_values_clause of each partition must have at least one value. If the table is partitioned on one key column, then use the upper branch of the list_values syntax to specify a list of values for that column. In this case, no value, including NULL, can appear in more than one partition. If the table is partitioned on multiple key columns, then use the lower branch of the list_values syntax to specify a list of value lists. Each value list is enclosed in parentheses and represents a list of values for the key columns. In this case, individual key column values can appear in more than one partition; however, no complete value list can appear in more than one partition. List partitions are not ordered.

If you specify the literal NULL for a partition value in the VALUES clause, then to access data in that partition in subsequent queries, you must use an IS NULL condition in the WHERE clause, rather than a comparison condition.

The DEFAULT keyword creates a partition into which the database will insert any row that does not map to another partition. Therefore, you can specify DEFAULT for only one partition, and you cannot specify any other values for that partition. Further, the default partition must be the last partition you define. The use of DEFAULT is similar to the use of MAXVALUE for range partitions.

The string comprising the list of values for each partition can be up to 4K bytes. The total number of values for all partitions cannot exceed 64K-1.

The partitioning key column for a list partition can be an extended data type column, which has a maximum size of 32767 bytes. In this case, the list of values that you want to specify for a partition may exceed the 4K byte limit. You can work around this limitation by using one of the following methods:

Restriction on the list_values_clause

You cannot specify a DEFAULT partition for an automatic list-partitioned table.

table_partition_description

The subclauses of the table_partition_description have the same behavior as described for range partitions in table_partition_description.

hash_partitions

Use the hash_partitions clause to specify that the table is to be partitioned using the hash method. Oracle Database assigns rows to partitions using a hash function on values found in columns designated as the partitioning key. You can specify individual hash partitions, or you can specify how many hash partitions the database should create.

Restrictions on Hash Partitioning

Hash partitioning is subject to the restrictions listed in "Restrictions on Partitioning in General". The following additional restrictions apply:

column

Specify an ordered list of columns used to determine into which partition a row belongs (the partitioning key).

individual_hash_partitions

Use this clause to specify individual partitions by name.

Use the indexing_clause to set the indexing property for a hash partition. Refer to the indexing_clause for more information.

Restriction on Specifying Individual Hash Partitions

The only clauses you can specify in the partitioning_storage_clause are the TABLESPACE clause and table compression.

Note:

If your enterprise has or will have databases using different character sets, then use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets. Refer to Oracle Database Globalization Support Guide for more information on character set support.

hash_partitions_by_quantity

An alternative to defining individual partitions is to specify the number of hash partitions. In this case, the database assigns partition names of the form SYS_P n. The STORE IN clause lets you specify one or more tablespaces where the hash partition data is to be stored. The number of tablespaces need not equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

For both methods of hash partitioning, for optimal load balancing you should specify a number of partitions that is a power of 2. When you specify individual hash partitions, you can specify both TABLESPACE and table compression in the partitioning_storage_clause. When you specify hash partitions by quantity, you can specify only TABLESPACE. Hash partitions inherit all other attributes from table-level defaults.

The table_compression clause has the same function as described for the table_properties of the table as a whole.

The prefix_compression clause and the OVERFLOW clause have the same function as described for the index_org_table_clause.

Tablespace storage specified at the table level is overridden by tablespace storage specified at the partition level, which in turn is overridden by tablespace storage specified at the subpartition level.

In the individual_hash_partitions clause, the TABLESPACE clause of the partitioning_storage_clause determines tablespace storage only for the individual partition being created. In the hash_partitions_by_quantity clause, the STORE IN clause determines placement of partitions as the table is being created and the default storage location for subsequently added partitions.

Restriction on Specifying Hash Partitions by Quantity

You cannot specify the advanced_index_compression clause of the index_compression clause.

composite_range_partitions

Use the composite_range_partitions clause to first partition table by range, and then partition the partitions further into range, hash, or list subpartitions.

The INTERVAL clause has the same semantics for composite range partitioning that it has for range partitioning. Refer to "INTERVAL Clause" for more information.

Specify subpartition_by_range, subpartition_by_hash or subpartition_by_list to indicate the type of subpartitioning you want for each composite range partition. Within these clauses you can specify a subpartition template, which establishes default subpartition characteristics for subpartitions created as part of this statement or subsequently created subpartitions.

After establishing the type of subpartitioning you want for the table, and optionally a subpartition template, you must define at least one range partition.

Restrictions on Composite Range Partitioning

Regardless of the type of subpartitioning, composite range partitioning is subject to the following restrictions:

composite_list_partitions

Use the composite_list_partitions clause to first partition table by list, and then partition the partitions further into range, hash, or list subpartitions.

Specify subpartition_by_range, subpartition_by_hash or subpartition_by_list to indicate the type of subpartitioning you want for each composite list partition. Within these clauses you can specify a subpartition template, which establishes default subpartition characteristics for subpartitions created as part of this statement and for subsequently created subpartitions.

After establishing the type of subpartitioning you want for each composite partition, and optionally defining a subpartition template, you must define at least one list partition.

Specify AUTOMATIC to create an automatic list-range, list-list, list-hash, or list-interval composite-partitioned table. This type of table enables the database to create additional partitions on demand. The optional STORE IN clause lets you specify one or more tablespaces into which the database will store data for the automatically created partitions. The AUTOMATIC and STORE IN clauses have the same semantics here as they have for noncomposite list partitions. Refer to AUTOMATIC and STORE IN in the documentation on list_partitions for the full semantics of these clauses. Automatic composite-partitioned tables are subject to the restrictions listed in Restrictions on Composite List Partitioning and Restrictions on Automatic List Partitioning.

Restrictions on Composite List Partitioning

Composite list partitioning is subject to the same restrictions as described in "Restrictions on Composite Range Partitioning".

composite_hash_partitions

Use the composite_hash_partitions clause to first partition table using the hash method, and then partition the partitions further into range, hash, or list subpartitions.

Specify subpartition_by_range, subpartition_by_hash or subpartition_by_list to indicate the type of subpartitioning you want for each composite range partition. Within these clauses you can specify a subpartition template, which establishes default subpartition characteristics for subpartitions created as part of this statement or subsequently created subpartitions.

After establishing the type of subpartitioning you want for the table, you must specify individual_hash_partitions or hash_partitions_by_quantity.

Restrictions on Composite Hash Partitioning

Composite hash partitioning is subject to the same restrictions as described in "Restrictions on Composite Range Partitioning".

subpartition_template

The subpartition_template is an optional element of range, list, and hash subpartitioning. The template lets you define default subpartitions for each table partition. Oracle Database will create these default subpartition characteristics in any partition for which you do not explicitly define subpartitions. This clause is useful for creating symmetric partitions. You can override this clause by explicitly defining subpartitions at the partition level, in the range_subpartition_desc, list_subpartition_desc, individual_hash_subparts, or hash_subparts_by_quantity clause.

When defining subpartitions with a template, you can explicitly define range, list, or hash subpartitions, or you can define a quantity of hash subpartitions.

Note:

When you specify tablespace storage for the subpartition template, it does not override any tablespace storage you have specified explicitly for the partitions of table. To specify tablespace storage for subpartitions, do one of these things:

Restrictions on Subpartition Templates

Subpartition templates are subject to the following restrictions:

subpartition_by_range

Use the subpartition_by_range clause to indicate that the database should subpartition by range each partition in table. The subpartitioning column list is unrelated to the partitioning key but is subject to the same restrictions (see column).

You can use the subpartition_template to specify default subpartition characteristic values. See subpartition_template. The database uses these values for any subpartition in this partition for which you do not explicitly specify the characteristic.

You can also define range subpartitions individually for each partition using the range_subpartition_desc of range_partition_desc or list_partition_desc. If you omit both subpartition_template and the range_subpartition_desc, then the database creates a single MAXVALUE subpartition.

subpartition_by_list

Use the subpartition_by_list clause to indicate that the database should subpartition each partition in the table on lists of literal values from the column list. You can specify a maximum of 16 list subpartitioning key columns.

You can use the subpartition_template to specify default subpartition characteristic values. See subpartition_template. The database uses these values for any subpartition in this partition for which you do not explicitly specify the characteristic.

You can also define list subpartitions individually for each partition using the list_subpartition_desc of range_partition_desc or list_partition_desc. If you omit both subpartition_template and the list_subpartition_desc, then the database creates a single DEFAULT subpartition.

Restrictions on List Subpartitioning

List subpartitioning is subject to the same restrictions as described in Restrictions on Composite Range Partitioning.

subpartition_by_hash

Use the subpartition_by_hash clause to indicate that the database should subpartition by hash each partition in table. The subpartitioning column list is unrelated to the partitioning key but is subject to the same restrictions (see column).

You can define the subpartitions using the subpartition_template or the SUBPARTITIONS integer clause. See subpartition_template. In either case, for optimal load balancing you should specify a number of partitions that is a power of 2.

If you specify SUBPARTITIONS integer, then you determine the default number of subpartitions in each partition of table, and optionally one or more tablespaces in which they are to be stored. The default value is 1. If you omit both this clause and subpartition_template, then the database will create each partition with one hash subpartition.

Notes on Composite Partitions

The following notes apply to composite partitions:

reference_partitioning

Use this clause to partition the table by reference. Partitioning by reference is a method of equipartitioning the table being created (the child table) by a referential constraint to an existing partitioned table (the parent table). When you partition a table by reference, partition maintenance operations subsequently performed on the parent table automatically cascade to the child table. Therefore, you cannot perform partition maintenance operations on a reference-partitioned table directly.

If the parent table is an interval-partitioned table, then partitions in the reference-partitioned child table that correspond to interval partitions in the parent table will be created during inserts into the child table. When an interval partition in a child table is created, the partition name is inherited from the associated parent table partition. If the child table has a table-level default tablespace, then it will be used as the tablespace for the new interval partition. Otherwise, the tablespace will be inherited from the parent table partition. Refer to Oracle Database VLDB and Partitioning Guide for more information on referencing an interval-partitioned table.

constraint

The partitioning referential constraint must meet the following conditions:

reference_partition_desc

Use this optional clause to specify partition names and to define the physical and storage characteristics of the partition. The subclauses of the table_partition_description have the same behavior as described for range partitions in table_partition_description.

If you specify this clause when creating a reference-partitioned child table whose parent is an interval-partitioned table, then the partition descriptors are used for the child table's non-interval partitions. Partition descriptors cannot be specified for interval partitions.

Restrictions on Reference Partitioning

Reference partitioning is subject to the restrictions listed in Restrictions on Partitioning in General. The following additional restrictions apply:

system_partitioning

Use this clause to create system partitions. System partitioning does not entail any partitioning key columns, nor do system partitions have any range or list bounds or hash algorithms. Rather, they provide a way to equipartition dependent tables such as nested table or domain index storage tables with partitioned base tables.

Restrictions on System Partitioning

System partitioning is subject to the following restrictions:

consistent_hash_partitions

This clause is valid only for sharded tables. Use this clause to create consistent hash partitions.

Each sharding key column with a character data type must have one of the following declared collations: BINARY, USING_NLS_COMP, USING_NLS_SORT, or USING_NLS_SORT_CS.

consistent_hash_with_subpartitions

This clause is valid only for sharded tables. Use this clause to create consistent hash with subpartitions.

Each sharding key column with a character data type must have one of the following declared collations: BINARY, USING_NLS_COMP, USING_NLS_SORT, or USING_NLS_SORT_CS.

range_partitionset_clause

Use this clause to create a range partition set.

In the SUBPARTITION BY clause, within the subpartition_template clause, you cannot specify a tablespace for a subpartition. That is, for range, list, and individual hash subpartitions, you cannot specify the TABLESPACE clause of the partitioning_storage_clause, and in the hash_subpartitions_by_quantity clause, you cannot specify the STORE IN ( tablespace ) clause.

In the PARTITIONS AUTO clause, within the subpartition_template clause of the range_partitionset_desc clause, you can specify a tablespace for a subpartition.

Each super sharding or sharding key column with a character data type must have one of the following declared collations: BINARY, USING_NLS_COMP, USING_NLS_SORT, or USING_NLS_SORT_CS.

list_partitionset_clause

Use this clause to create a list partition set.

In the SUBPARTITION BY clause, within the subpartition_template clause, you cannot specify a tablespace for a subpartition. That is, for range, list, and individual hash subpartitions, you cannot specify the TABLESPACE clause of the partitioning_storage_clause, and in the hash_subpartitions_by_quantity clause, you cannot specify the STORE IN ( tablespace ) clause.

In the PARTITIONS AUTO clause, within the subpartition_template clause of the list_partitionset_desc clause, you can specify a tablespace for a subpartition.

Each super sharding or sharding key column with a character data type must have one of the following declared collations: BINARY, USING_NLS_COMP, USING_NLS_SORT, or USING_NLS_SORT_CS.

attribute_clustering_clause

Use this clause to enable the table for attribute clustering. Attribute clustering lets you cluster data in close physical proximity based on the content of specified columns.

Attribute clustering can be based only on columns in table or on joined values from other tables. The latter is called join attribute clustering.

clustering_join

Use this clause to specify join attribute clustering. Use the JOIN clause to specify the joined values from other tables on which to base the attribute clustering. You can specify a maximum of four JOIN clauses.

cluster_clause

Use this clause to specify the type of ordering to use for the table: linear ordering or interleaved ordering. If you do not specify the LINEAR or INTERLEAVED keyword, then the default is LINEAR.

BY LINEAR ORDER

Use this clause to specify linear ordering. This type of ordering stores data according to the order of the specified columns. If you specify this clause, then you can specify only one clustering column group, which can contain at most 10 columns.

BY INTERLEAVED ORDER

Use this clause to specify interleaved ordering. This type of ordering uses a special multidimensional clustering technique, similar to z-ordering, that permits multicolumn clustering. If you specify this clause, then you can specify at most four clustering column groups, with a maximum of 40 columns across all groups.

clustering_columns

Use this clause to specify one or more clustering column groups.

clustering_column_group

Use this clause to specify one or more columns to be included in the clustering column group.

Restriction on Attribute Clustering Columns

Each character column in the clustering column group must have one of the following declared collations: BINARY or USING_NLS_COMP.

clustering_when

Use these clauses to allow or disallow attribute clustering during direct-path insert operations and data movement operations.

ON LOAD

Specify YES ON LOAD to allow, or NO ON LOAD to disallow, attribute clustering during direct-path inserts (serial or parallel) resulting either from an INSERT or a MERGE operation.

The default is YES ON LOAD.

ON DATA MOVEMENT

Specify YES ON DATA MOVEMENT to allow, or NO ON DATA MOVEMENT to disallow, attribute clustering for data movement that occurs during the following operations:

The default is YES ON DATA MOVEMENT.

zonemap_clause

Use this clause to create a zone map on the table. The zone map tracks the columns specified in the clustering_columns clause.

If you subsequently drop the table or use the ALTER TABLE statement to DROP CLUSTERING or MODIFY CLUSTERING ... WITHOUT MATERIALIZED ZONEMAP, then the zone map will be dropped.

Restrictions on Attribute Clustering

The following restrictions apply to attribute clustering:

CACHE | NOCACHE | CACHE READS

Use these clauses to indicate how Oracle Database should store blocks in the buffer cache. For LOB storage, you can specify CACHE, NOCACHE, or CACHE READS. For other types of storage, you can specify only CACHE or NOCACHE.

If you omit these clauses, then:

The behavior of CACHE and NOCACHE described in this section does not apply when Oracle Database chooses to use direct reads or to perform table scans using parallel query.

CACHE

For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.

See Also:

Oracle Database Concepts for more information on how the database maintains the least recently used (LRU) list

As a parameter in the LOB_storage_clause, CACHE specifies that the database places LOB data values in the buffer cache for faster access. The database evaluates this parameter in conjunction with the logging_clause. If you omit this clause, then the default value for both BasicFiles and SecureFiles LOBs is NOCACHE LOGGING.

Restriction on CACHE

You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.

NOCACHE

For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.

As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB values are not brought into the buffer cache. NOCACHE is the default for LOB storage.

Restriction on NOCACHE

You cannot specify NOCACHE for an index-organized table.

CACHE READS

CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations but not during write operations.

logging_clause

Use this clause to indicate whether the storage of data blocks should be logged or not.

See Also:

logging_clause for a description of the logging_clause when specified as part of LOB_parameters

result_cache_clause

Use this clause to determine whether the results of statements or query blocks that name this table are considered for storage in the result cache.

You can use mode DEFAULT or mode FORCE for result caching, with STANDBY enabled or disabled.

You can query the RESULT_CACHE column of the DBA_, ALL_, and USER_TABLES data dictionary views to learn the result cache mode of the table.

The RESULT_CACHE and NO_RESULT_CACHE SQL hints take precedence over these result cache table annotations and the RESULT_CACHE_MODE initialization parameter. The RESULT_CACHE_MODE setting of FORCE in turn takes precedence over this table annotation clause.

Note:

The RESULT_CACHE_MODE setting of FORCE is not recommended, as it can cause significant performance and latching overhead, as database and clients will try to cache all queries.

See Also:

parallel_clause

The parallel_clause lets you parallelize creation of the table and set the default degree of parallelism for queries and the DML INSERT, UPDATE, DELETE, and MERGE after table creation.

Note:

The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. The superseded syntax is still supported for backward compatibility, but may result in slightly different behavior from that documented.

NOPARALLEL

Specify NOPARALLEL for serial execution. This is the default.

PARALLEL

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

PARALLEL integer

Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

NOROWDEPENDENCIES | ROWDEPENDENCIES

This clause lets you specify whether table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created.

ROWDEPENDENCIES

Specify ROWDEPENDENCIES if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.

Restriction on the ROWDEPENDENCIES Clause

Oracle does not support table compression for tables that use row-level dependency tracking. If you specify both the ROWDEPENDENCIES clause and the table_compression clause, then the table_compression clause is ignored. To remove the ROWDEPENDENCIES attribute, you must redefine the table using the DBMS_REDEFINITION package or recreate the table.

NOROWDEPENDENCIES

Specify NOROWDEPENDENCIES if you do not want table to use the row-level dependency tracking feature. This is the default.

enable_disable_clause

The enable_disable_clause lets you specify whether Oracle Database should apply a constraint. By default, constraints are created in ENABLE VALIDATE state.

Restrictions on Enabling and Disabling Constraints

Enabling and disabling constraints are subject to the following restrictions:

ENABLE Clause

Use this clause if you want the constraint to be applied to the data in the table. This clause is described fully in "ENABLE Clause" in the documentation on constraints.

DISABLE Clause

Use this clause if you want to disable the integrity constraint. This clause is described fully in "DISABLE Clause" in the documentation on constraints.

UNIQUE

The UNIQUE clause lets you enable or disable the unique constraint defined on the specified column or combination of columns.

PRIMARY KEY

The PRIMARY KEY clause lets you enable or disable the primary key constraint defined on the table.

CONSTRAINT

The CONSTRAINT clause lets you enable or disable the integrity constraint named constraint_name.

KEEP | DROP INDEX

This clause lets you either preserve or drop the index Oracle Database has been using to enforce a unique or primary key constraint.

Restriction on Preserving and Dropping Indexes

You can specify this clause only when disabling a unique or primary key constraint.

using_index_clause

The using_index_clause lets you specify an index for Oracle Database to use to enforce a unique or primary key constraint, or lets you instruct the database to create the index used to enforce the constraint.

See Also:

CASCADE

Specify CASCADE to disable any integrity constraints that depend on the specified integrity constraint. To disable a primary or unique key that is part of a referential integrity constraint, you must specify this clause.

Restriction on CASCADE

You can specify CASCADE only if you have specified DISABLE.

row_movement_clause

The row_movement_clause lets you specify whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data.

Note:

If you need static rowids for data access, then do not enable row movement. For a normal (heap-organized) table, moving a row changes the rowid of the row. For a moved row in an index-organized table, the logical rowid remains valid, although the physical guess component of the logical rowid becomes inaccurate.

If you omit this clause, then the database disables row movement.

Restriction on Row Movement

You cannot specify this clause for a nonpartitioned index-organized table.

logical_replication_clause

You can perform partial database replication for users such as Oracle GoldenGate, and reduce the supplemental logging overhead of uninteresting tables in interesting schema where supplemental logging is enabled.

When logical replication is enabled for a table, supplemental logging of all levels (table/schema/database levels) is honored.

When logical replication is disabled for a table, it means that only database level supplemental logging is honored. This provides a way for partial database replication users (who will not enable database level column data supplemental logging) to disable supplemental logging for uninteresting tables, so that even when supplemental logging is enabled at the schema level, there is no column data supplemental logging for uninteresting tables.

If you create a table without the logical_replication_clause, logical replication is not disabled and supplemental logging of all levels is honored. No additional ID or scheduling-key supplemental logging is added for this table.

If you create a table with DISABLE LOGICAL REPLICATION, logical replication is disabled for this table. Table-level and schema-level supplemental logging directives are ignored.

If you create a table with ENABLE LOGICAL REPLICATION ALL KEYS, ID and scheduling-key (PK, UI, FK, ALLKEYS) supplemental logging is implicitly enabled for the table.

If you create a table with ENABLE LOGICAL REPLICATION ALLOW NOVALIDATE KEYS, ID and scheduling-key is implicitly enabled for the table. Primary key constraint in NOVALIDATE mode can be supplementally logged as a unique identifier for the table.

flashback_archive_clause

You must have the FLASHBACK ARCHIVE object privilege on the specified flashback archive to specify this clause. Use this clause to enable or disable historical tracking for the table.

Restrictions on flashback_archive_clause

Flashback data archives are subject to the following restrictions:

See Also:

ROW ARCHIVAL

Specify this clause to enable table for row archival. This clause lets you implement In-Database Archiving, which allows you to designate table rows as active or archived. You can then perform queries on only the active rows within the table.

When you specify this clause, a hidden column ORA_ARCHIVE_STATE is created in the table. The column is of data type VARCHAR2. You can specify a value of 0 or 1 for this column to indicate whether a row is active (0) or archived (1). If you do not specify a value for ORA_ARCHIVE_STATE when inserting data into the table, then the value is set to 0.

See Also:

FOR EXCHANGE WITH TABLE

This clause lets you create a table that matches the structure of an existing partitioned table. The two tables are then eligible for exchanging partitions and subpartitions. For table, specify an existing partitioned table. For schema, specify the schema that contains the existing partitioned table. If you omit schema, then the database assumes the table is in your own schema.

This operation creates a metadata clone, without data, of the partitioned table. The clone has the same column ordering and column properties of the original table. Column properties copied to the clone during this operation include unusable columns, invisible columns, virtual expression columns, functional index expression columns, and other internal settings and attributes. Indexes on the existing partitioned table are not created on the clone table.

You can subsequently use the exchange_partition_subpart clause of ALTER TABLE to exchange partitions or subpartitions between the two tables. Refer to exchange_partition_subpart in the documentation on ALTER TABLE for more information.

Restrictions on FOR EXCHANGE WITH TABLE

Each super sharding or sharding key column with a character data type must have one of the following declared collations: BINARY, USING_NLS_COMP, USING_NLS_SORT, or USING_NLS_SORT_CS.

The following restrictions apply to the FOR EXCHANGE WITH TABLE clause:

AS subquery

Specify a subquery to determine the contents of the table. The rows returned by the subquery are inserted into the table upon its creation.

For object tables, subquery can contain either one expression corresponding to the table type, or the number of top-level attributes of the table type. Refer to SELECT for more information.

If subquery returns the equivalent of part or all of an existing materialized view, then the database may rewrite the query to use the materialized view in place of one or more tables specified in subquery.

Oracle Database derives data types and lengths from the subquery. Oracle Database follows the following rules for integrity constraints and other column and table attributes:

If each column returned by subquery has a column name or is an expression with a specified column alias, then you can omit the columns from the table definition entirely. In this case, the names of the columns of table are the same as the columns in subquery. The exception is creating an index-organized table, for which you must specify the columns in the table definition because you must specify a primary key column.

You can use subquery in combination with the TO_LOB function to convert the values in a LONG column in another table to LOB values in a column of the table you are creating.

See Also:

parallel_clause

If you specify the parallel_clause in this statement, then the database will ignore any value you specify for the INITIAL storage parameter and will instead use the value of the NEXT parameter.

ORDER BY

The ORDER BY clause lets you order rows returned by the subquery.

When specified with CREATE TABLE, this clause does not necessarily order data across the entire table. For example, it does not order across partitions. Specify this clause if you intend to create an index on the same key as the ORDER BY key column. Oracle Database will cluster data on the ORDER BY key so that it corresponds to the index key.

Restrictions on the Defining Query of a Table

The table query is subject to the following restrictions:

object_table

The OF clause lets you explicitly create an object table of type object_type. The columns of an object table correspond to the top-level attributes of type object_type. Each row will contain an object instance, and each instance will be assigned a unique, system-generated object identifier when a row is inserted. If you omit schema, then the database creates the object table in your own schema.

Object tables, as well as XMLType tables, object views, and XMLType views, do not have any column names specified for them. Therefore, Oracle defines a system-generated pseudocolumn OBJECT_ID. You can use this column name in queries and to create object views with the WITH OBJECT IDENTIFIER clause.

object_table_substitution

Use the object_table_substitution clause to specify whether row objects corresponding to subtypes can be inserted into this object table.

NOT SUBSTITUTABLE AT ALL LEVELS

NOT SUBSTITUTABLE AT ALL LEVELS indicates that the object table being created is not substitutable. In addition, substitution is disabled for all embedded object attributes and elements of embedded nested tables and arrays. The default is SUBSTITUTABLE AT ALL LEVELS.

See Also:

object_properties

The properties of object tables are essentially the same as those of relational tables. However, instead of specifying columns, you specify attributes of the object.

For attribute, specify the qualified column name of an item in an object.

oid_clause

The oid_clause lets you specify whether the object identifier of the object table should be system generated or should be based on the primary key of the table. The default is SYSTEM GENERATED.

Restrictions on the oid_clause

This clause is subject to the following restrictions:

Note:

A primary key object identifier is locally unique but not necessarily globally unique. If you require a globally unique identifier, then you must ensure that the primary key is globally unique.

oid_index_clause

This clause is relevant only if you have specified the oid_clause as SYSTEM GENERATED. It specifies an index, and optionally its storage characteristics, on the hidden object identifier column.

For index, specify the name of the index on the hidden system-generated object identifier column. If you omit index, then the database generates a name.

physical_properties and table_properties

The semantics of these clauses are documented in the corresponding sections under relational tables. See physical_properties and table_properties.

XMLType_table

Use the XMLType_table syntax to create a table of data type XMLType. Most of the clauses used to create an XMLType table have the same semantics that exist for object tables. The clauses specific to XMLType tables are described in this section.

Object tables, as well as XMLType tables, object views, and XMLType views, do not have any column names specified for them. Therefore, Oracle defines a system-generated pseudocolumn OBJECT_ID. You can use this column name in queries and to create object views with the WITH OBJECT IDENTIFIER clause.

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.

The optional STORE ALL VARRAYS AS clause lets you specify how all varrays in the XMLType table or column are to be stored.

The optional ALLOW | DISALLOW clauses are valid only if you have specified BINARY XML storage.

See Also:

MEMOPTIMIZE FOR READ

Use this clause to enable fast lookup. Fast lookup improves the performance high frequency data query operations. The MEMOPTIMIZE_POOL_SIZE initialization parameter controls the size of the memoptimize pool. Note that the feature uses additional memory from the SGA.

MEMOPTIMIZE FOR WRITE

Use this clause to enable fast ingest. Fast ingest optimizes memory processing of high frequency single row data inserts from Internet of Things (IoT) applications.

Restrictions

Blockchain and immutable tables do not support MEMOPTIMZE FOR WRITE.

Columns of BFILE datatype do not support MEMOPTIMZE FOR WRITE.

PARENT

You can use this clause to create a child table in a sharded table family.

A sharded table family is a set of tables that are sharded in the same way. Corresponding partitions of all tables in a table family are stored in the same shard. This enables you to minimize the number of multishard joins when querying data in the table family.

There are two methods for creating a sharded table family. The recommended method involves using reference partitioning. However, if it is impossible or undesirable to create the primary and foreign key constraints that are required for reference partitioning, then you can use the PARENT clause to create a sharded table family.

The rules for creating a sharded table family differ depending on which method you use. When you create a sharded table family by using the PARENT clause, the following rules apply:

You can create multiple sharded table families with system sharding but at most one with composite or user-defined sharding.

Examples

Creating Tables: General Examples

This statement shows how the employees table owned by the sample human resources (hr) schema was created. A hypothetical name is given to the table and constraints so that you can duplicate this example in your test database:

CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn_demo NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn_demo NOT NULL , phone_number VARCHAR2(20) , hire_date DATE DEFAULT SYSDATE CONSTRAINT emp_hire_date_nn_demo NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn_demo NOT NULL , salary NUMBER(8,2) CONSTRAINT emp_salary_nn_demo NOT NULL , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) , dn VARCHAR2(300) , CONSTRAINT emp_salary_min_demo CHECK (salary > 0) , CONSTRAINT emp_email_uk_demo UNIQUE (email) ) ;

This table contains twelve columns. The employee_id column is of data type NUMBER. The hire_date column is of data type DATE and has a default value of SYSDATE. The last_name column is of type VARCHAR2 and has a NOT NULL constraint, and so on.

Creating a Table: Storage Example

To define the same employees_demo table in the example tablespace with a small storage capacity, issue the following statement:

CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn_demo NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn_demo NOT NULL , phone_number VARCHAR2(20) , hire_date DATE DEFAULT SYSDATE CONSTRAINT emp_hire_date_nn_demo NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn_demo NOT NULL , salary NUMBER(8,2) CONSTRAINT emp_salary_nn_demo NOT NULL , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) , dn VARCHAR2(300) , CONSTRAINT emp_salary_min_demo CHECK (salary > 0) , CONSTRAINT emp_email_uk_demo UNIQUE (email) ) TABLESPACE example STORAGE (INITIAL 8M);

Creating a Table with a DEFAULT ON NULL Column Value: Example

The following statement creates a table myemp, which can be used to store employee data. The department_id column is defined with a DEFAULT ON NULL column value of 50. Therefore, if a subsequent INSERT statement attempts to assign a NULL value to department_id, then the value of 50 will be assigned instead.

CREATE TABLE myemp (employee_id number, last_name varchar2(25), department_id NUMBER DEFAULT ON NULL 50 NOT NULL);

In the employees table, employee_id 178 has a NULL value for department_id:

SELECT employee_id, last_name, department_id FROM employees WHERE department_id IS NULL;

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID

    178 Grant

Populate the myemp table with the employee_id, last_name, and department_id column data from the employees table:

INSERT INTO myemp (employee_id, last_name, department_id) (SELECT employee_id, last_name, department_id from employees);

In the myemp table, employee_id 178 has a value of 50 for department_id:

SELECT employee_id, last_name, department_id FROM myemp WHERE employee_id = 178;

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID

    178 Grant                                50

Creating a Table with an Identity Column: Examples

The following statement creates a table t1 with an identity column id. The sequence generator will always assign increasing integer values to id, starting with 1.

CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY);

The following statement creates a table t2 with an identity column id. The sequence generator will, by default, assign increasing integer values to id in increments of 10 starting with 100.

CREATE TABLE t2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10));

Creating a Table: Temporary Table Example

The following statement creates a temporary table today_sales for use by sales representatives in the sample database. Each sales representative session can store its own sales data for the day in the table. The temporary data is deleted at the end of the session.

CREATE GLOBAL TEMPORARY TABLE today_sales ON COMMIT PRESERVE ROWS AS SELECT * FROM orders WHERE order_date = SYSDATE;

Creating a Table with Deferred Segment Creation: Example

The following statement creates a table with deferred segment creation. Oracle Database will not create a segment for the data of this table until data is inserted into the table:

CREATE TABLE later (col1 NUMBER, col2 VARCHAR2(20)) SEGMENT CREATION DEFERRED;

Substitutable Table and Column Examples

The following statements create a type hierarchy, which can be used to create a substitutable table. Type employee_t inherits the name and ssn attributes from type person_t and in addition has department_id and salary attributes. Type part_time_emp_t inherits all of the attributes from employee_t and, through employee_t, those of person_t and in addition has a num_hrs attribute. Type part_time_emp_t is final by default, so no further subtypes can be created under it.

CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER) NOT FINAL; /

CREATE TYPE employee_t UNDER person_t (department_id NUMBER, salary NUMBER) NOT FINAL; /

CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs NUMBER); /

The following statement creates a substitutable table from the person_t type:

CREATE TABLE persons OF person_t;

The following statement creates a table with a substitutable column of type person_t:

CREATE TABLE books (title VARCHAR2(100), author person_t);

When you insert into persons or books, you can specify values for the attributes of person_t or any of its subtypes. Examples of insert statements appear in "Inserting into a Substitutable Tables and Columns: Examples".

You can extract data from such tables using built-in functions and conditions. For examples, see the functions TREAT and SYS_TYPEID, and the "IS OF type Condition" condition.

Creating a Table: Parallelism Examples

The following statement creates a table using an optimum number of parallel execution servers to scan employees and to populate dept_80:

CREATE TABLE dept_80 PARALLEL AS SELECT * FROM employees WHERE department_id = 80;

Using parallelism speeds up the creation of the table, because the database 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.

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

CREATE TABLE dept_80 AS SELECT * FROM employees WHERE department_id = 80;

Creating a Table: ENABLE/DISABLE Examples

The following statement shows how the sample table departments was created. The example defines a NOT NULL constraint, and places it in ENABLE VALIDATE state. A hypothetical name is given to the table so that you can duplicate this example in your test database:

CREATE TABLE departments_demo ( department_id NUMBER(4) , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) , dn VARCHAR2(300) ) ;

The following statement creates the same departments_demo table but also defines a disabled primary key constraint:

CREATE TABLE departments_demo ( department_id NUMBER(4) PRIMARY KEY DISABLE , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) , dn VARCHAR2(300) ) ;

Nested Table Example

The following statement shows how the sample table pm.print_media was created with a nested table column ad_textdocs_ntab:

CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;

Creating a Table: Multilevel Collection Example

The following example shows how an account manager might create a table of customers using two levels of nested tables:

CREATE TYPE phone AS OBJECT (telephone NUMBER); / CREATE TYPE phone_list AS TABLE OF phone; / CREATE TYPE my_customers AS OBJECT ( cust_name VARCHAR2(25), phones phone_list); / CREATE TYPE customer_list AS TABLE OF my_customers; / CREATE TABLE business_contacts ( company_name VARCHAR2(25), company_reps customer_list) NESTED TABLE company_reps STORE AS outer_ntab (NESTED TABLE phones STORE AS inner_ntab);

The following variation of this example shows how to use the COLUMN_VALUE keyword if the inner nested table has no column or attribute name:

CREATE TYPE phone AS TABLE OF NUMBER;
/ CREATE TYPE phone_list AS TABLE OF phone; / CREATE TABLE my_customers ( name VARCHAR2(25), phone_numbers phone_list) NESTED TABLE phone_numbers STORE AS outer_ntab (NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);

Creating a Table: LOB Column Example

The following statement is a variation of the statement that created the pm.print_media table with some added LOB storage characteristics:

CREATE TABLE print_media_new ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_new LOB (ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE example STORAGE (INITIAL 6144) CHUNK 4000 NOCACHE LOGGING);

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

Index-Organized Table Example

The following statement is a variation of the sample table hr.countries, which is index organized:

CREATE TABLE countries_demo ( country_id CHAR(2) CONSTRAINT country_id_nn_demo NOT NULL , country_name VARCHAR2(40) , currency_name VARCHAR2(25) , currency_symbol VARCHAR2(3) , region VARCHAR2(15) , CONSTRAINT country_c_id_pk_demo PRIMARY KEY (country_id ) ) ORGANIZATION INDEX INCLUDING country_name PCTTHRESHOLD 2 STORAGE ( INITIAL 4K ) OVERFLOW STORAGE ( INITIAL 4K );

External Table Example

The following statement creates an external table that represents a subset of the sample table hr.departments. The TYPE clause specifies that the access driver type for the table is ORACLE_LOADER. The ACCESS PARAMETERS() clause specifies parameter values for the ORACLE_LOADER access driver. These parameters are shown in italics and form the opaque_format_spec. The syntax for opaque_format_spec depends on the access driver type and is outside the scope of this document. Refer to Oracle Database Utilities for details on the ORACLE_LOADER access driver and the opaque_format_spec syntax.

CREATE TABLE dept_external ( deptno NUMBER(6), dname VARCHAR2(20), loc VARCHAR2(25) ) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY admin ACCESS PARAMETERS ( RECORDS DELIMITED BY newline BADFILE 'ulcase1.bad' DISCARDFILE 'ulcase1.dis' LOGFILE 'ulcase1.log' SKIP 20 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ( deptno INTEGER EXTERNAL(6), dname CHAR(20), loc CHAR(25) ) ) LOCATION ('ulcase1.ctl') ) REJECT LIMIT UNLIMITED;

XMLType Examples

This section contains brief examples of creating an XMLType table or XMLType column. For a more expanded version of these examples, refer to "Using XML in SQL Statements".

XMLType Table Examples

The following example creates a very simple XMLType table with one implicit binary XML column:

CREATE TABLE xwarehouses OF XMLTYPE;

The following example creates an XMLSchema-based table. The XMLSchema must already have been created (see "Using XML in SQL Statements" for more information):

CREATE TABLE xwarehouses OF XMLTYPE XMLSCHEMA "http://www.example.com/xwarehouses.xsd" ELEMENT "Warehouse";

You can define constraints on an XMLSchema-based table, and you can also create indexes on XMLSchema-based tables, which greatly enhance subsequent queries. You can create object-relational views on XMLType tables, and you can create XMLType views on object-relational tables.

See Also:

XMLType Column Examples

The following example creates a table with an XMLType column stored as a CLOB. This table does not require an XMLSchema, so the content structure is not predetermined:

CREATE TABLE xwarehouses ( warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS CLOB (TABLESPACE example STORAGE (INITIAL 6144) CHUNK 4000 NOCACHE LOGGING);

The following example creates a similar table, but stores XMLType data in an object relational XMLType column whose structure is determined by the specified schema:

CREATE TABLE xwarehouses ( warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS OBJECT RELATIONAL XMLSCHEMA "http://www.example.com/xwarehouses.xsd" ELEMENT "Warehouse";

The following example creates another similar table with an XMLType column stored as a SecureFiles CLOB. This table does not require an XMLSchema, so the content structure is not predetermined. SecureFiles LOBs require a tablespace with automatic segment-space management, so the example uses the tablespace created in "Specifying Segment Space Management for a Tablespace: Example".

CREATE TABLE xwarehouses ( warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS SECUREFILE CLOB (TABLESPACE auto_seg_ts STORAGE (INITIAL 6144) CACHE);

Partitioning Examples

Range Partitioning Example

The sales table in the sample schema sh is partitioned by range. The following example shows an abbreviated variation of the sales table. Constraints and storage elements have been omitted from the example.

CREATE TABLE range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')), PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')), PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')), PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')), PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')), PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')), PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)) ;

For information about partitioned table maintenance operations, see Oracle Database VLDB and Partitioning Guide.

Range Partitioning Live SQL Example

The following statement creates a table partitioned by range:

CREATE TABLE empl_h
(
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE,
job_id VARCHAR2(10),
salary NUMBER(8, 2),
part_name VARCHAR2(25)
) PARTITION BY RANGE (hire_date) (
PARTITION hire_q1 VALUES less than(to_date('01-APR-2014', 'DD-MON-YYYY')),
PARTITION hire_q2 VALUES less than(to_date('01-JUL-2014', 'DD-MON-YYYY')),
PARTITION hire_q3 VALUES less than(to_date('01-OCT-2014', 'DD-MON-YYYY')),
PARTITION hire_q4 VALUES less than(to_date('01-JAN-2015', 'DD-MON-YYYY'))
);

The following statements insert rows into the partitions:

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)
VALUES (1, 'Jane', 'Doe', 'example.com', '415.555.0100', '10-Feb-2014', '1001', 5001,'HIRE_Q1');

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)
VALUES (2, 'John', 'Doe', 'example.net', '415.555.0101', '10-Apr-2014', '1002', 7001,'HIRE_Q2');

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)
VALUES (3, 'Isabelle', 'Owl', 'example.org', '415.555.0102', '10-Sep-2014', '1003', 10001,'HIRE_Q3');

INSERT INTO empl_h (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, Part_name)
VALUES (4, 'Smith', 'Jones', 'example.in', '415.555.0103', '10-Dec-2014', '1004', 12001,'HIRE_Q4');

The following statements display the partition names using data dictionary tables:

SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'EMPL_H';

PARTITION_NAME

HIRE_Q1 HIRE_Q2 HIRE_Q3 HIRE_Q4

SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS FROM USER_PART_TABLES WHERE TABLE_NAME = 'EMPL_H';

TABLE_NAME PARTITIONING_TYPE STATUS

EMPL_H RANGE VALID

The following statement creates a table named parts by selecting a particular column from the data dictionary table user_tab_partitions:

CREATE TABLE parts (p_name) AS SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'EMPL_H';

The following statement displays the table data:

select * from parts;

P_NAME

HIRE_Q1 HIRE_Q2 HIRE_Q3 HIRE_Q4

The following statement compares the columns from the two tables and displays the information based on the comparison:

select E.HIRE_DATE,E.JOB_ID,P.p_name from empl_h E, parts P where E.Part_name = P.p_name;

HIRE_DATE JOB_ID P_NAME

10-FEB-14 1001 HIRE_Q1 10-APR-14 1002 HIRE_Q2 10-SEP-14 1003 HIRE_Q3 10-DEC-14 1004 HIRE_Q4

Interval Partitioning Example

The following example creates a variation of the oe.customers table that is partitioned by interval on the credit_limit column. One range partition is created to establish the transition point. All of the original data in the table is within the bounds of the range partition. Then data is added that exceeds the range partition, and the database creates a new interval partition.

CREATE TABLE customers_demo ( customer_id number(6), cust_first_name varchar2(20), cust_last_name varchar2(20), credit_limit number(9,2)) PARTITION BY RANGE (credit_limit) INTERVAL (1000) (PARTITION p1 VALUES LESS THAN (5001));

INSERT INTO customers_demo (customer_id, cust_first_name, cust_last_name, credit_limit) (select customer_id, cust_first_name, cust_last_name, credit_limit from customers);

Query the USER_TAB_PARTITIONS data dictionary view before the database creates the interval partition:

SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'CUSTOMERS_DEMO';

PARTITION_NAME HIGH_VALUE

P1 5001

Insert data into the table that exceeds the high value of the range partition:

INSERT INTO customers_demo VALUES (699, 'Fred', 'Flintstone', 5500);

Query the USER_TAB_PARTITIONS view again after the insert to learn the system-generated name of the interval partition created to accommodate the inserted data. (The system-generated name will vary for each session.)

SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'CUSTOMERS_DEMO' ORDER BY partition_name;

PARTITION_NAME HIGH_VALUE

P1 5001 SYS_P44 6001

List Partitioning Example

The following statement shows how the sample table oe.customers might have been created as a list-partitioned table. Some columns and all constraints of the sample table have been omitted in this example.

CREATE TABLE list_customers ( customer_id NUMBER(6) , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(20) , cust_address CUST_ADDRESS_TYP , nls_territory VARCHAR2(30) , cust_email VARCHAR2(40)) PARTITION BY LIST (nls_territory) ( PARTITION asia VALUES ('CHINA', 'THAILAND'), PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'), PARTITION west VALUES ('AMERICA'), PARTITION east VALUES ('INDIA'), PARTITION rest VALUES (DEFAULT));

Partitioned Table with LOB Columns Example

This statement creates a partitioned table print_media_demo with two partitions p1 and p2, and a number of LOB columns. The statement uses the sample table pm.print_media.

CREATE TABLE print_media_demo ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_demo LOB (ad_composite, ad_photo, ad_finaltext) STORE AS(STORAGE (INITIAL 20M)) PARTITION BY RANGE (product_id) (PARTITION p1 VALUES LESS THAN (3000) TABLESPACE tbs_01 LOB (ad_composite, ad_photo) STORE AS (TABLESPACE tbs_02 STORAGE (INITIAL 10M)) NESTED TABLE ad_textdocs_ntab STORE AS nt_p1 (TABLESPACE example), PARTITION P2 VALUES LESS THAN (MAXVALUE) LOB (ad_composite, ad_finaltext) STORE AS SECUREFILE (TABLESPACE auto_seg_ts) NESTED TABLE ad_textdocs_ntab STORE AS nt_p2 ) TABLESPACE tbs_03;

Partition p1 will be in tablespace tbs_01. The LOB data partitions for ad_composite and ad_photo will be in tablespace tbs_02. The LOB data partition for the remaining LOB columns will be in tablespace tbs_01. The storage attribute INITIAL is specified for LOB columns ad_composite and ad_photo. 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 tbs_02 for columns ad_composite and ad_photo and from tablespace tbs_01 for the remaining LOB columns. 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. The nested table partition for ad_textdocs_ntab will be stored as nt_p1 in tablespace example.

Partition p2 will be in the default tablespace tbs_03. The LOB data for ad_composite and ad_finaltext will be in tablespace auto_seg_ts as SecureFiles LOBs. The LOB data for the remaining LOB columns will be in tablespace tbs_03. The LOB index for columns ad_composite and ad_finaltext will be in tablespace auto_seg_ts. The LOB index for the remaining LOB columns will be in tablespace tbs_03. The nested table partition for ad_textdocs_ntab will be stored as nt_p2 in the default tablespace tbs_03.

Hash Partitioning Example

The sample table oe.product_information is not partitioned. However, you might want to partition such a large table by hash for performance reasons, as shown in this example. The tablespace names are hypothetical in this example.

CREATE TABLE hash_products ( product_id NUMBER(6) PRIMARY KEY , product_name VARCHAR2(50) , product_description VARCHAR2(2000) , category_id NUMBER(2) , weight_class NUMBER(1) , warranty_period INTERVAL YEAR TO MONTH , supplier_id NUMBER(6) , product_status VARCHAR2(20) , list_price NUMBER(8,2) , min_price NUMBER(8,2) , catalog_url VARCHAR2(50) , CONSTRAINT product_status_lov_demo CHECK (product_status in ('orderable' ,'planned' ,'under development' ,'obsolete') ) ) PARTITION BY HASH (product_id) PARTITIONS 4 STORE IN (tbs_01, tbs_02, tbs_03, tbs_04);

Reference Partitioning Example

The next statement uses the hash_products partitioned table created in the preceding example. It creates a variation of the oe.order_items table that is partitioned by reference to the hash partitioning on the product id of hash_products. The resulting child table will be created with five partitions. For each row of the child table part_order_items, the database evaluates the foreign key value (product_id) to determine the partition number of the parent table hash_products to which the referenced key belongs. The part_order_items row is placed in its corresponding partition.

CREATE TABLE part_order_items ( order_id NUMBER(12) PRIMARY KEY, line_item_id NUMBER(3), product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2), quantity NUMBER(8), CONSTRAINT product_id_fk FOREIGN KEY (product_id) REFERENCES hash_products(product_id)) PARTITION BY REFERENCE (product_id_fk);

Composite-Partitioned Table Examples

The table created in the "Range Partitioning Example" divides data by time of sale. If you plan to access recent data according to distribution channel as well as time, then composite partitioning might be more appropriate. The following example creates a copy of that range_sales table but specifies range-hash composite partitioning. The partitions with the most recent data are subpartitioned with both system-generated and user-defined subpartition names. Constraints and storage attributes have been omitted from the example.

CREATE TABLE composite_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (channel_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')), PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')), PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')), PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')), PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')) SUBPARTITIONS 8, PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')) (SUBPARTITION ch_c, SUBPARTITION ch_i, SUBPARTITION ch_p, SUBPARTITION ch_s, SUBPARTITION ch_t), PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) SUBPARTITIONS 4) ;

The following examples creates a partitioned table of customers based on the sample table oe.customers. In this example, the table is partitioned on the credit_limit column and list subpartitioned on the nls_territory column. The subpartition template determines the subpartitioning of any subsequently added partitions, unless you override the template by defining individual subpartitions. This composite partitioning makes it possible to query the table based on a credit limit range within a specified region:

CREATE TABLE customers_part ( customer_id NUMBER(6), cust_first_name VARCHAR2(20), cust_last_name VARCHAR2(20), nls_territory VARCHAR2(30), credit_limit NUMBER(9,2)) PARTITION BY RANGE (credit_limit) SUBPARTITION BY LIST (nls_territory) SUBPARTITION TEMPLATE (SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'), SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'), SUBPARTITION other VALUES (DEFAULT)) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2500), PARTITION p3 VALUES LESS THAN (MAXVALUE));

Object Column and Table Examples

Creating Object Tables: Examples

Consider object type department_typ:

CREATE TYPE department_typ AS OBJECT ( d_name VARCHAR2(100), d_address VARCHAR2(200) ); /

Object table departments_obj_t holds department objects of type department_typ:

CREATE TABLE departments_obj_t OF department_typ;

The following statement creates object table salesreps with a user-defined object type, salesrep_typ:

CREATE OR REPLACE TYPE salesrep_typ AS OBJECT ( repId NUMBER, repName VARCHAR2(64));

CREATE TABLE salesreps OF salesrep_typ;

Creating a Table with a User-Defined Object Identifier: Example

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

CREATE TYPE employees_typ AS OBJECT (e_no NUMBER, e_address CHAR(30)); /

CREATE TABLE employees_obj_t OF employees_typ (e_no PRIMARY KEY) OBJECT IDENTIFIER IS PRIMARY KEY;

You can subsequently reference the employees_obj_t object table using either inline_ref_constraint or out_of_line_ref_constraint syntax:

CREATE TABLE departments_t (d_no NUMBER, mgr_ref REF employees_typ SCOPE IS employees_obj_t);

CREATE TABLE departments_t ( d_no NUMBER, mgr_ref REF employees_typ CONSTRAINT mgr_in_emp REFERENCES employees_obj_t);

Specifying Constraints on Type Columns: Example

The following example shows how to define constraints on attributes of an object type column:

CREATE TYPE address_t 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_t, 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') );