CREATE TABLE

Purpose

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

• A relational table is the basic structure to hold user data.

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

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

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

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

Additional Topics

• Prerequisites

• Syntax

• Keywords and Parameters

• Examples

Prerequisites

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

In addition to the table privileges above, to create 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, the owner must have been granted the EXECUTE privileges to the referenced types with the GRANT OPTION, or have the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. Without these privileges, the table owner has insufficient privileges to grant access on the table to other users.

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

See Also: CREATE INDEX Oracle8i Application Developer's Guide - Fundamentals for more information about the privileges required to create tables using types

Syntax

relational_table::=

object_table::=

relational_properties::=

object_properties::=

physical_properties::=

table_properties::=

subquery::= See SELECT and subquery.

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

OID_clause::=

OID_index_clause::=

segment_attributes_clause:=

row_movement_clause::=

physical_attributes_clause::=

storage_clause: See the storage_clause.

index_organized_table_clause::=

compression_clause::=

index_organized_overflow_clause::=

LOB_storage_clause::=

LOB_parameters::=

varray_storage_clause::=

nested_table_storage_clause::=

range_partitioning_clause::=

composite_partitioning_clause::=

partition_definition::=

subpartition_clause::=

partition_level_subpartitioning::=

hash_partitioning_clause::=

hash_partitioning_storage_clause::=

parallel_clause::=

enable_disable_clause::=

using_index_clause::=

global_index_clause::=

global_partition_clause::=

Keywords and Parameters

GLOBAL TEMPORARY

Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.

A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below).

See Also: Oracle8i Concepts for information on temporary tables

Restrictions:

• Temporary tables cannot be partitioned, index-organized, or clustered.

• You cannot specify any referential integrity (foreign key) constraints on temporary tables.

• Temporary tables cannot contain columns of nested table or varray type.

• You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.

• Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)

• You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.

• Distributed transactions are not supported for temporary tables.

schema

Specify the schema to contain the table. If you omit schema, Oracle creates the table in your own schema.

table

Specify the name of the table (or object table) to be created.

OF object_type

The OF clause lets you explicitly creates 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 (OID) when a row is inserted. If you omit schema, Oracle creates the object table in your own schema.

Objects residing in an object table are referenceable.

See Also: CREATE TYPE for more information about creating objects "User-Defined Type Categories", "User-Defined Functions", "Expressions", CREATE TYPE, and Oracle8i Administrator's Guide for more information about using REF types

relational_properties

column	Specify the name of a column of the table. If you also specify AS subquery, you can omit column and datatype unless you are creating an index-organized table (IOT). If you specify AS subquery when creating an IOT, you must specify column, and you must omit datatype.
	The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect "hidden columns" that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, please refer to Oracle8i Administrator's Guide.
datatype	Specify the datatype of a column. See Also: "Datatypes"for information on Oracle-supplied datatypes Restrictions: You cannot specify a LOB column or a column of type VARRAY for a partitioned index-organized table (IOT). The datatypes for nonpartitioned IOTs are not restricted. You can specify a column of type ROWID, but Oracle does not guarantee that the values in such columns are valid rowids.
	Note: You can omit datatype under these conditions: If you also specify AS subquery. (If you are creating an index-organized table and you specify AS subquery, you must omit the datatype.) If the statement also designates the column as part of a foreign key in a referential integrity constraint. (Oracle automatically assigns to the column the datatype of the corresponding column of the referenced key of the referential integrity constraint.)
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 datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression. Restriction: A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified. See Also: "Expressions" for the syntax of expr
table_ref_constraint and column_ref_constraint	These clauses let you further describe a column of type REF. The only difference between these clauses is that you specify table_ref from the table level, so you must identify the REF column or attribute you are defining. You specify column_ref after you have already identified the REF column or attribute. See Also: constraint_clause for syntax and description of these constraints
column_constraint	Use the column_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. See Also: the syntax description of column_constraint in the constraint_clause
table_constraint	Use the table_constraint to define an integrity constraint as part of the table definition. See Also: the syntax description of table_constraint in the constraint_clause
	Note: You must specify a PRIMARY KEY constraint for an index-organized table, and it cannot be DEFERRABLE.

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.

attribute

Specify the qualified column name of an item in an object.

ON COMMIT

The ON COMMIT clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.

DELETE ROWS	Specify DELETE ROWS for a transaction-specific temporary table (this is the default). Oracle will truncate the table (delete all its rows) after each commit.
PRESERVE ROWS	Specify PRESERVE ROWS for a session-specific temporary table. Oracle will truncate the table (delete all its rows) when you terminate the session.

OID_clause

The OID_clause lets you specify whether the object identifier (OID) 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:

• You cannot specify OBJECT IDENTIFIER IS PRIMARY KEY unless you have already specified a PRIMARY KEY constraint for the table.

• You cannot specify this clause for a nested table.

  Note: A primary key OID is locally (but not necessarily globally) unique. If you require a globally unique identifier, 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.

index

Specify the name of the index on the hidden system-generated object identifier column. If not specified, Oracle generates a name.

physical_properties

segment_attributes_clause

physical_attributes_clause	The physical_attributes_clause lets you specify the value of the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and the storage characteristics of the table.
	For a nonpartitioned table, each parameter and storage characteristic you specify determines the actual physical attribute of the segment associated with the table. For partitioned tables, the value you specify for the parameter or storage characteristic is the default physical attribute of the segments associated with all partitions specified in this CREATE statement (and in subsequent ALTER TABLE ... ADD PARTITION statements), unless you explicitly override that value in the PARTITION clause of the statement that creates the partition.
PCTFREE integer	Specify the percentage of space in each data block of the table, object table OID index, or partition reserved for future updates to the table's rows. The value of PCTFREE must be a value from 0 to 99. A value of 0 allows the entire block to be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.
	PCTFREE has the same function in the PARTITION description and in the statements that create and alter clusters, indexes, materialized views, and materialized view logs. The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new blocks.
PCTUSED integer	Specify the minimum percentage of used space that Oracle maintains for each data block of the table, object table OID index, or index-organized table overflow data segment. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 0 to 99 and defaults to 40.
	PCTUSED has the same function in the PARTITION description and in the statements that create and alter clusters, materialized views, and materialized view logs.
	PCTUSED is not a valid table storage characteristic for an index-organized table (ORGANIZATION INDEX).
	The sum of PCTFREE and PCTUSED must be equal to or less than 100. You can use PCTFREE and PCTUSED together to utilize space within a table more efficiently. See Also: Oracle8i Performance Guide and Reference for information on the performance effects of different values PCTUSED and PCTFREE
INITRANS integer	Specify the initial number of transaction entries allocated within each data block allocated to the table, object table OID index, partition, LOB index segment, or overflow data segment. This value can range from 1 to 255 and defaults to 1. In general, you should not change the INITRANS value from its default.
	Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system.
	This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
	The INITRANS parameter serves the same purpose in the PARTITION description, clusters, indexes, materialized views, and materialized view logs as in tables. The minimum and default INITRANS value for a cluster or index is 2, rather than 1.
MAXTRANS integer	Specify the maximum number of concurrent transactions that can update a data block allocated to the table, object table OID index, partition, LOB index segment, or index-organized overflow data segment. This limit does not apply to queries. This value can range from 1 to 255 and the default is a function of the data block size. You should not change the MAXTRANS value from its default.
	If the number of concurrent transactions updating a block exceeds the INITRANS value, Oracle dynamically allocates transaction entries in the block until either the MAXTRANS value is exceeded or the block has no more free space.
	The MAXTRANS parameter serves the same purpose in the PARTITION description, clusters, materialized views, and materialized view logs as in tables.
storage_clause	The storage_clause lets you specify storage characteristics for the table, object table OID index, partition, LOB storage, LOB index segment, or index-organized table overflow data segment. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. See Also: the
TABLESPACE	Specify the tablespace in which Oracle creates the table, object table OID index, partition, LOB storage, LOB index segment, or index-organized table overflow data segment. If you omit TABLESPACE, then Oracle creates that item in the default tablespace of the owner of the schema containing the table.
	For heap-organized tables with one or more LOB columns, if you omit the TABLESPACE clause for LOB storage, Oracle creates the LOB data and index segments in the tablespace where the table is created. However, for an index-organized table with one or more LOB columns, if you omit TABLESPACE, the LOB data and index segments are created in the tablespace in which the primary key index segment of the index-organized table is created.
	For nonpartitioned tables, the value specified for TABLESPACE is the actual physical attribute of the segment associated with the table. For partitioned tables, the value specified for TABLESPACE is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and on subsequent ALTER TABLE ... ADD PARTITION statements), unless you specify TABLESPACE in the PARTITION description.
	See Also: CREATE TABLESPACE for more information on tablespaces
LOGGING | NOLOGGING	Specify whether the creation of the table (and 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 that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against the table, partition, or LOB storage are logged (LOGGING) or not logged (NOLOGGING).
	For a table or table partition, if you omit this clause, the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
	For LOBs, if you omit this clause, If you specify CACHE, then LOGGING is used (because you cannot have CACHE NOLOGGING). If you specify NOCACHE or CACHE READS, the logging attribute defaults to the logging attribute of the tablespace in which it resides. NOLOGGING does not apply to LOBs that are stored inline with row data. That is, if you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data.
	For nonpartitioned tables, the value specified for LOGGING is the actual physical attribute of the segment associated with the table. For partitioned tables, the logging attribute value specified is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and in subsequent ALTER TABLE ... ADD PARTITION statements), unless you specify LOGGING|NOLOGGING in the PARTITION description.
	In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALID and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. Therefore, if you cannot afford to lose this table, you should take a backup after the NOLOGGING operation.
	The size of a redo log generated for an operation in NOLOGGING mode is significantly smaller than the log generated with the LOGGING attribute set. If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the LOGGING operation restores the table. However, media recovery from a backup taken before the NOLOGGING operation does not restore the table.
	See Also: Oracle8i Concepts and Oracle8i Administrator's Guide for more information about logging and parallel DML
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 Corporation strongly recommends that you use the LOGGING and NOLOGGING keywords.
	Restrictions: You cannot specify RECOVERABLE for partitioned tables or LOB storage characteristics. You cannot specify UNRECOVERABLE for a partitioned or index-organized tables. You can specify UNRECOVERABLE only with AS subquery.

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.

index_organized_table_clause

Use the index_organized_table_clause to instruct Oracle to maintain the table rows (both primary key column values and non-key column values) in a B*-tree index built on the primary key. Index-organized tables are therefore best suited for primary key-based access and manipulation. An index-organized table is an alternative to

• A nonclustered table indexed on the primary key by using the CREATE INDEX statement

• A clustered table stored in an indexed cluster that has been created using the CREATE CLUSTER statement that maps the primary key for the table to the cluster key

Restrictions:

• You cannot specify a column of type ROWID for an index-organized table.

• A partitioned index-organized table cannot contain columns of LOB or varray type. (This restriction does not apply to nonpartitioned index-organized tables.)

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

  PCTTHRESHOLD integer

  Specify the percentage of space reserved in the index block for an index-organized table row. All trailing columns of a row, starting with the column that causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50. If you do not specify PCTTHRESHOLD, the default is 50. Restrictions: PCTTHRESHOLD must be large enough to hold the primary key. You cannot specify PCTTHRESHOLD for individual partitions of an index-organized table. See Also: the INCLUDING clause of the index_organized_table_clause

  compression_clause	The compression_clause lets you enable or disable key compression.
  	COMPRESS	Specify COMPRESS to enable key compression, which eliminates repeated occurrence of primary key column values in index-organized tables. Use integer to specify the prefix length (number of prefix columns to compress). The valid range of prefix length values is from 1 to the number of primary key columns minus 1. The default prefix length is the number of primary key columns minus 1. Restriction: At the partition level, you can specify COMPRESS, but you cannot specify the prefix length with integer.
  	NOCOMPRESS	Specify NOCOMPRESS to disable key compression in index-organized tables. This is the default.
  index_organized_overflow_clause	The index_organized_overflow_clause lets you instruct Oracle that index-organized table data rows exceeding the specified threshold are placed in the data segment specified in this clause.
  	When you create an index-organized table, Oracle evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified OVERFLOW, Oracle raises an error and does not execute the CREATE TABLE statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking. All physical attributes and storage characteristics you specify in this clause after the OVERFLOW keyword apply only to the overflow segment of the table. Physical attributes and storage characteristics for the index-organized table itself, default values for all its partitions, and values for individual partitions must be specified before this keyword. If the index-organized table contains one or more LOB columns, the LOBs will be stored out-of-line unless you specify OVERFLOW, even if they would otherwise be small enough be to stored inline.

  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. Restriction: You cannot specify this clause for individual partitions of an index-organized table.

  Note: If an attempt to divide a row at column_name causes the size of the index portion of the row to exceed the PCTTHRESHOLD value (either specified or default), Oracle breaks up the row based on the PCTTHRESHOLD value.

CLUSTER

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

See Also: CREATE CLUSTER

Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name.

A clustered table uses the cluster's space allocation. Therefore, do not use the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE clause, or the storage_clause with the CLUSTER clause.

Restrictions: Object tables and tables containing LOB columns cannot be part of a cluster.

LOB_storage_clause

The LOB_storage_clause lets you specify the storage attributes of LOB data segments.

• For a nonpartitioned table (that is, when specified in the physical_properties clause without any of the partitioning clauses), this clause specifies the table's storage attributes of LOB data segments.

• For a partitioned table specified at the table level (that is, when specified in the physical_properties clause along with one of the partitioning clauses), this clause specifies the default storage attributes for LOB data segments associated with each partition or subpartition. These storage attributes apply to all partitions or subpartitions unless overridden by a LOB_storage_clause at the partition or subpartition level.

• For an individual partition of a partitioned table (that is, when specified as part of a partition_definition), this clause specifies the storage attributes of the data segments of that partition or the default storage attributes of any subpartitions of this partition. A partition-level LOB_storage_clause overrides a table-level LOB_storage_clause.

• For an individual subpartition of a partitioned table (that is, when specified as part of a subpartition_clause), this clause specifies the storage attributes of the data segments of this subpartition. A subpartition-level LOB_storage_clause overrides both partition-level and table-level LOB_storage_clauses.

Restriction: You cannot specify the LOB_index_clause if table is partitioned.

See Also: Oracle8i Application Developer's Guide - Large Objects (LOBs) for detailed information about LOBs, including guidelines for creating gigabyte LOBs "LOB Column Example"

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 automatically creates a system-managed index for each LOB_item you create.
LOB_segname	Specify the name of the LOB data segment. You cannot use LOB_segname if you specify more than one LOB_item.
LOB_parameters	The LOB_parameters clause lets you specify various elements of LOB storage.
	ENABLE STORAGE IN ROW	If you enable storage in row, 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: For an index-organized table, you cannot specify this parameter unless you have specified an OVERFLOW segment in the index_organized_table_clause.
	DISABLE STORAGE IN ROW	If you disable storage in row, the LOB value is stored outside of the row regardless of the length of the LOB value.
	The LOB locator is always stored in the row 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 Also: move_table_clause of ALTER TABLE.
	CHUNK integer	Specify the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, Oracle rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and integer is 2050, Oracle allocates 4096 bytes (2 blocks). The maximum value is 32768 (32K), which is the largest Oracle block size allowed. The default CHUNK size is one Oracle database block. You cannot change the value of CHUNK once it is set.
		Note: 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, Oracle returns an error.
	PCTVERSION integer	Specify the maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
LOB_index_clause	This clause is deprecated as of Oracle8i. Oracle generates an index for each LOB column. Oracle names and manages the LOB indexes internally. Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data. See Also: Oracle8i Migration for information on how Oracle manages LOB indexes in tables migrated from earlier versions

varray_storage_clause

The varray_storage_clause lets you specify separate storage characteristics for the LOB in which a varray will be stored. In addition, if you specify this clause, Oracle will always store the varray in a LOB, even if it is small enough to be stored inline.

• For a nonpartitioned table (that is, when specified in the physical_properties clause without any of the partitioning clauses), this clause specifies the storage attributes of the varray's LOB data segments.

• For a partitioned table specified at the table level (that is, when specified in the physical_properties clause along with one of the partitioning clauses), this clause specifies the default storage attributes for the varray's LOB data segments associated with each partition (or its subpartitions, if any).

• For an individual partition of a partitioned table (that is, when specified as part of a partition_definition), this clause specifies the storage attributes of the varray's LOB data segments of that partition or the default storage attributes of the varray's LOB data segments of any subpartitions of this partition. A partition-level varray_storage_clause overrides a table-level varray_storage_clause.

• For an individual subpartition of a partitioned table (that is, when specified as part of a subpartition_clause), this clause specifies the storage attributes of the varray's data segments of this subpartition. A subpartition-level varray_storage_clause overrides both partition-level and table-level varray_storage_clauses.

Restriction: You cannot specify the TABLESPACE parameter of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the containing table's tablespace.

nested_table_storage_clause

The nested_table_storage_clause lets you to specify separate storage characteristics for a nested table, which in turn enables you to define the nested table as an index-organized table. The storage table is created in the same tablespace as its parent table (using the default storage characteristics) and stores the nested table values of the column for which it was created.

You must include this clause when creating a table with columns or column attributes whose type is a nested table. (Clauses within this clause that function the same way they function for parent object tables are not repeated here.)

Restrictions:

• You cannot specify this clause for a temporary table.

• You cannot specify the OID_clause.

• You cannot specify TABLESPACE (as part of the segment_attributes_clause) for a nested table. The tablespace is always that of the parent table.

• At create time, you cannot specify (as part of object_properties) a table_ref_constraint, column_ref_constraint, or referential constraint for the attributes of a nested table. However, you can modify a nested table to add such constraints using ALTER TABLE.

• You cannot query or perform DML statements on the storage table directly, but you can modify the nested table column storage characteristics by using the name of storage table in an ALTER TABLE statement.

  See Also: ALTER TABLE for information about modifying nested table column storage characteristics

  nested_item	Specify the name of a column (or a top-level attribute of the table's object type) whose type is a nested table.
  storage_table	Specify the name of the table where the rows of nested_item reside. For a nonpartitioned table, the storage table is created in the same schema and the same tablespace as the parent table. For a partitioned table, the storage table is created in the default tablespace of the schema.
  	Restriction: You cannot partition the storage table of a nested table.
  	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 returns as the result of a query. VALUE returns a copy of the nested table itself. LOCATOR returns a collection locator to the copy of the nested table.
  	Note: The locator is scoped to the session and cannot be used across sessions. Unlike a LOB locator, the collection locator cannot be used to modify the collection instance.
  If you do not specify the segment_attributes_clause or the LOB_storage_clause, the nested table is heap organized and is created with default storage characteristics.

table_properties

range_partitioning_clause

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

column_list

Specify an ordered list of columns used to determine into which partition a row belongs (the partitioning key). Restriction: The columns in column_list can be of any built-in datatype except ROWID, LONG, or LOB.

hash_partitioning_clause

Use the hash_partitioning_clause to specify that the table is to be partitioned using the hash method. Oracle assigns rows to partitions using a hash function on values found in columns designated as the partitioning key.

See Also: Oracle8i Concepts for more information on hash partitioning

column_list

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

Restrictions:

• You cannot specify more than 16 columns in column_list.

• The column_list cannot contain the ROWID or UROWID pseudocolumns.

• The columns in column_list can be of any built-in datatype except ROWID, LONG, or LOB.

  Note: If your enterprise has or will have databases using different character sets, use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets.

  See Also: Oracle8i National Language Support Guide for more information on character set support

You can specify hash partitioning in one of two ways:

• You can specify the number of partitions. In this case, Oracle assigns partition names of the form SYS_Pnnn. The STORE IN clause specifies one or more tablespaces where the hash partitions are to be stored. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.

• Alternatively, you can specify individual partitions by name. The TABLESPACE clause specifies where the partition should be stored.

  Note: The only attribute you can specify for hash partitions (or subpartitions) is TABLESPACE. Hash partitions inherit all other attributes from table-level defaults. Hash subpartitions inherit any attributes specified at the partition level, and inherit all other attributes from the table-level defaults. 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.

composite_partitioning_clause

Use the composite_partitioning_clause to first partition table by range, and then partition the partitions further into hash subpartitions. This combination of range partitioning and hash subpartitioning is called composite partitioning.

subpartition_clause	Use the subpartition_clause to indicate that Oracle 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.
SUBPARTITIONS quantity	Specify 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 do not specify the subpartition_clause here, Oracle will create each partition with one hash subpartition unless you subsequently specify the partition_level_hash_subpartitioning clause.

partition_definition

PARTITION partition	Specify the physical partition attributes. If partition is omitted, Oracle generates a name with the form SYS_Pn for the partition. The partition must conform to the rules for naming schema objects and their part as described in "Schema Object Naming Rules".
	Notes: You can specify up to 64K-1 partitions and 64K-1 subpartitions. For a discussion of factors that might impose practical limits less than this number, please refer to Oracle8i Administrator's Guide. You can create a partitioned table with just one partition. Note, however, that a partitioned table with one partition is different from a nonpartitioned table. For instance, you cannot add a partition to a nonpartitioned table.
VALUES LESS THAN value_list	Specify the noninclusive upper bound for the current partition. value_list is an ordered list of literal values corresponding to column_list in the partition_by_range_clause. You can substitute the keyword MAXVALUE for any literal in 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. See Also: Oracle8i Concepts for more information about partition bounds
	Note: If table is partitioned on a DATE column, and if the NLS date format does not specify the first two digits of the year, you must use the TO_DATE function with the YYYY 4-character format mask for the year. (The RRRR format mask is not supported.) The NLS date format is determined implicitly by NLS_TERRITORY or explicitly by NLS_DATE_FORMAT. See Also: - Oracle8i National Language Support Guide for more information on these initialization parameters - "Partitioned Table Example"
LOB_storage_clause	The LOB_storage_clause lets you specify LOB storage characteristics for one or more LOB items in this partition. If you do not specify the LOB_storage_clause for a LOB item, Oracle generates a name for each LOB data partition. The system-generated names for LOB data and LOB index partitions take the form SYS_LOB_Pn and SYS_IL_Pn, respectively, where P stands for "partition" and n is a system-generated number.
varray_storage_clause	The varray_storage_clause lets you specify storage characteristics for one or more varray items in this partition.
partition_level_subpartitioning	The partition_level_subpartitioning clause lets you specify hash subpartitions for partition. This clause overrides the default settings established in the subpartition_clause. Restriction: You can specify this clause only for a composite-partitioned table.
	You can specify individual subpartitions by name, and optionally the tablespace where each should be stored, or You can specify the number of subpartitions (and optionally one or more tablespaces where they are to be stored). In this case, Oracle assigns subpartition names of the form SYS_SUBPnnn. The number of tablespaces does not have to equal the number of subpartitions. If the number of partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.

row_movement_clause

The row_movement_clause lets you specify whether a row can be moved to a different partition or subpartition because of a change to one or more of its key values during an update operation.

Restriction: You can specify this clause only for a partitioned table.

ENABLE	Specify ENABLE to allow Oracle to move a row to a different partition or subpartition as the result of an update to the partitioning or subpartitioning key.
	Caution: Moving a row in the course of an UPDATE operation changes that row's ROWID.
DISABLE	Specify DISABLE if you want Oracle to return an error if an update to a partitioning or subpartitioning key would result in a row moving to a different partition or subpartition. This is the default.

CACHE | NOCACHE | CACHE READS

CACHE	For data that will be accessed frequently, specify CACHE to indicate that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This clause is useful for small lookup tables.
	As a parameter in the LOB_storage_clause, CACHE specifies that Oracle places the LOB values in the buffer cache for faster access. Restriction: You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.
NOCACHE	For data that will not be accessed frequently, specify NOCACHE to indicate 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. This is the default.
	As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list. (The latter is the default behavior.) Restriction: You cannot specify NOCACHE for an index-organized table.
	Note: NOCACHE has no effect on tables for which you specify KEEP in the storage_clause.
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.

MONITORING | NOMONITORING

MONITORING	Specify MONITORING if you want modification statistics to be collected on this table. These statistics are estimates of the number of rows affected by DML statements over a particular period of time. They are available for use by the optimizer or for analysis by the user. Restriction: You cannot specify MONITORING for a temporary table.
NOMONITORING	Specify NOMONITORING if you do not want Oracle to collect modification statistics on the table. This is the default. Restriction: You cannot specify NOMONITORING for a temporary table.

parallel_clause

The parallel_clause lets you parallelize creation of the table and set the default degree of parallelism for queries and DML on the table after creation.

Note: The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior.

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.

Notes on the parallel_clause

• If table contains any columns of LOB or user-defined object type, this statement as well as subsequent INSERT, UPDATE, or DELETE operations on table are executed serially without notification. Subsequent queries, however, will be executed in parallel.

• For partitioned index-organized tables, CREATE TABLE ... AS SELECT is executed serially, as are subsequent DML operations. Subsequent queries, however, will be executed in parallel.

• A parallel hint overrides the effect of the parallel_clause.

• DML statements and CREATE TABLE ... AS SELECT statements that reference remote objects can run in parallel. However, the "remote object" must really be on a remote database. The reference cannot loop back to an object on the local database (for example, by way of a synonym on the remote database pointing back to an object on the local database).

  See Also: Oracle8i Performance Guide and Reference, Oracle8i Concepts, and Oracle8i Parallel Server Concepts for more information on parallelized operations

enable_disable_clause

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

Restrictions:

• To enable or disable any integrity constraint, you must have defined the constraint in this or a previous statement.

• You cannot enable a referential integrity constraint unless the referenced unique or primary key constraint is already enabled.

  See Also: constraint_clause for more information on constraints

  ENABLE	Specify ENABLE if you want the constraint to be applied to all new data in the table. VALIDATE additionally specifies that all old data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid. If any row in the table violates the integrity constraint, the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, Oracle enables the constraint. Subsequently, if new data violates the constraint, Oracle does not execute the statement and returns an error indicating the integrity constraint violation.
  	If you place a primary key constraint in ENABLE VALIDATE mode, the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before enabling the table's primary key constraint. (For optimal results, do this before entering data into the column.)
  	NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock. If you specify neither VALIDATE nor NOVALIDATE, the default is VALIDATE.
  	If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, so Oracle rebuilds the index every time the constraint is enabled. To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.
  	If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE, the operation can be performed in parallel, and does not block reads, writes, or other DDL operations. Restriction: You cannot enable a foreign key that references a unique or primary key that is disabled.
  DISABLE	Specify DISABLE to disable the integrity constraint. Disabled integrity constraints appear in the data dictionary along with enabled constraints. If you do not specify this clause when creating a constraint, Oracle automatically enables the constraint.
  	DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.
  	If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index.
  	DISABLE NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). See Also: Oracle8i Performance Guide and Reference for information on when to use this setting You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is in DISABLE NOVALIDATE state. Further, the optimizer can use constraints in DISABLE NOVALIDATE state.
  	If you specify neither VALIDATE nor NOVALIDATE, the default is NOVALIDATE. If you disable a unique or primary key constraint that is using a unique index, Oracle drops the unique index.
  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 table's primary key constraint.
  CONSTRAINT	The CONSTRAINT clause lets you enable or disable the integrity constraint named constraint.

using_index_clause

The using_index_clause lets you specify parameters for the index Oracle creates to enforce a unique or primary key constraint. Oracle gives the index the same name as the constraint.

You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index. These parameters are described earlier in this statement. If table is partitioned, you can specify a locally or globally partitioned index for the unique or primary key constraint.

See Also: CREATE INDEX for a description of LOCAL and the global_index_clause, and for a description of NOSORT and LOGGING|NOLOGGING in relation to indexes

Restriction: Use these parameters only when enabling unique and primary key constraints.

EXCEPTIONS INTO

Specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named EXCEPTIONS. The exceptions table must be on your local database.

You can create the EXCEPTIONS table using one of these scripts:

• UTLEXCPT.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)

• UTLEXPT1.SQL uses universal rowids, so it can accommodate rows from both conventional and index-organized tables.

If you create your own exceptions table, it must follow the format prescribed by one of these two scripts.

See Also: Oracle8i Migration for compatibility issues related to the use of these scripts

Note: If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), you must create a separate exceptions table for each index-organized table to accommodate its primary-key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script.

See Also: The DBMS_IOT package in Oracle8i Supplied PL/SQL Packages Reference for information on the SQL scripts Oracle8i Performance Guide and Reference for information on eliminating migrated and chained rows

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: You can specify CASCADE only if you have specified DISABLE.

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.

See Also: SELECT and subquery

Restrictions:

• The number of columns in the table must equal the number of expressions in the subquery.

• The column definitions can specify only column names, default values, and integrity constraints, not datatypes.

• You cannot define a referential integrity constraint in a CREATE TABLE statement that contains AS subquery. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement.

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

See Also: for information on these parameters

Oracle derives datatypes and lengths from the subquery. Oracle also follows the following rules for integrity constraints:

• Oracle automatically defines any NOT NULL constraints on columns in the new table that existed on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column.

• If a CREATE TABLE statement contains both AS subquery and a CONSTRAINT clause or an ENABLE clause with the EXCEPTIONS INTO clause, Oracle ignores AS subquery. If any rows violate the constraint, Oracle does not create the table and returns an error.

If all expressions in subquery are columns, rather than expressions, 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.

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: Oracle8i Migration for a discussion of why and when to copy LONGs to LOBs "Conversion Functions" for a description of how to use the TO_LOB function

Note: If subquery returns (in part or totally) the equivalent of an existing materialized view, Oracle may use the materialized view (for query rewrite) in place of one or more tables specified in subquery. See Also: Oracle8i Data Warehousing Guide for more information on materialized views and query rewrite

order_by_clause	The ORDER BY clause lets you order rows returned by the statements. See also: SELECT and subquery for more information on the order_by_clause
	Note: When specified with CREATE TABLE, this clause does not necessarily order data cross 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 will cluster data on the ORDER BY key so that it corresponds to the index key.

Examples

General Example

To define the emp table owned by scott, you could issue the following statement:

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

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

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

Temporary Table Example

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

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

Storage Example

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

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

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

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

PARALLEL Example

The following statement creates a table using an optimum number of parallel execution servers to scan scott.emp and to populate emp_dept:

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

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

NOPARALLEL Example

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

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

ENABLE VALIDATE Example

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

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

DISABLE Example

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

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

EXCEPTIONS INTO Example

The following example creates the order_exceptions table to hold rows from an index-organized table orders that violate integrity constraint check_orders:

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

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

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

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

See Also: INSERT SELECT and subquery for information on the privileges necessary to insert rows into tables

Nested Table Example

The following statement creates relational table employee with a nested table column projects:

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

LOB Column Example

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

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

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

Index-Organized Table Example

The following statement creates an index-organized table:

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

Partitioned Table Example

The following statement creates a table with three partitions:

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

See Also: Oracle8i Administrator's Guide for information about partitioned table maintenance operations

Partitioned Table with LOB Columns Example

This statement creates a partitioned table pt with two partitions p1 and p2, and three LOB columns, b, c, and d:

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

Partition p1 will be in tablespace ts1. The LOB data partitions for b and d will be in tablespace tsa. The LOB data partition for c will be in tablespace ts1. The storage attribute INITIAL is specified for LOB columns b and d; other attributes will be inherited from the default table-level specification. The default LOB storage attributes not specified at the table level will be inherited from the tablespace tsa for columns b and d and tablespace ts1 for column c. LOB index partitions will be in the same tablespaces as the corresponding LOB data partitions. Other storage attributes will be based on values of the corresponding attributes of the LOB data partitions and default attributes of the tablespace where the index partitions reside.

Partition p2 will be in the default tablespace tsx. The LOB data for b and c will be in tablespace tsb. The LOB data for d will be in tablespace tsx. The LOB index for columns b and c will be in tablespace tsb. The LOB index for column d will be in tablespace tsx.

Hash-Partitioned Table Example

This statement creates a table partitioned by hash on columns containing data about chemicals. The hash partitions are stored in tablespaces tbs1, tbs2, tbs3, and tbs4:

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

Composite-Partitioned Table Example

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

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

Object Table Examples

Consider object type dept_t:

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

Object table dept holds department objects of type dept_t:

CREATE TABLE dept OF dept_t; 

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

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

Nested Table Example

The following statement creates relational table employee with a nested table column projects:

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

REF Example

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

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

CREATE TABLE dept OF dept_t;

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

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

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

User-Defined OID Example

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

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

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

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

Constraints on Type Columns Example

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

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

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

PARALLEL Example

The following statement creates a table using 10 parallel execution servers, 5 to scan scott.emp and another 5 to populate emp_dept:

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