Skip Headers
Oracle® Database SQL Reference
10g Release 2 (10.2)

Part Number B14200-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE TABLE

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:


Additional Topics

Prerequisites

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

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

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

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

To create an external table, you must have the required read and write operating system privileges on the appropriate operating system directories. You must have the READ object privilege on the database directory object corresponding to the operating system directory in which the external data resides. You must also have the WRITE object privilege on the database directory in which the files will reside if you specify a log file or bad file in the opaque_format_spec or if you unload data into an external table from a database table by specifying the AS subquery clause.


See Also:


Syntax

create_table::=

Description of create_table.gif follows
Description of the illustration create_table.gif

(relational_table::=, object_table ::=, XMLType_table ::=)

relational_table::=

Description of relational_table.gif follows
Description of the illustration relational_table.gif


Note:

Each of the clauses following the table name is optional for any given relational table. However, for every table you must at least specify either column names and datatypes using the relational_properties clause or an AS subquery clause using the table_properties clause.

(relational_properties::=, physical_properties::=, table_properties::=)

object_table ::=

Description of object_table.gif follows
Description of the illustration object_table.gif

(object_table_substitution ::=, object_properties::=, oid_clause::=, oid_index_clause::=, physical_properties::=, table_properties::=)

XMLType_table ::=

Description of XMLType_table.gif follows
Description of the illustration XMLType_table.gif

(XMLType_storage::=, XMLSchema_spec::=, oid_clause::=, oid_index_clause::=, physical_properties::=, table_properties::=)

relational_properties::=

Description of relational_properties.gif follows
Description of the illustration relational_properties.gif

(column_definition::=, constraint::=, supplemental_logging_props ::=)

column_definition::=

Description of column_definition.gif follows
Description of the illustration column_definition.gif

(encryption_spec::=, constraint::=)

encryption_spec::=

Description of encryption_spec.gif follows
Description of the illustration encryption_spec.gif

object_table_substitution ::=

Description of object_table_substitution.gif follows
Description of the illustration object_table_substitution.gif

object_properties::=

Description of object_properties.gif follows
Description of the illustration object_properties.gif

(constraint::=, supplemental_logging_props ::=)

oid_clause::=

Description of oid_clause.gif follows
Description of the illustration oid_clause.gif

oid_index_clause::=

Description of oid_index_clause.gif follows
Description of the illustration oid_index_clause.gif

(physical_attributes_clause::=)

physical_properties::=

Description of physical_properties.gif follows
Description of the illustration physical_properties.gif

(segment_attributes_clause ::=, table_compression::=, index_org_table_clause ::=, external_table_clause ::=)

segment_attributes_clause ::=

Description of segment_attributes_clause.gif follows
Description of the illustration segment_attributes_clause.gif

(physical_attributes_clause::=, logging_clause::=)

physical_attributes_clause::=

Description of physical_attributes_clause.gif follows
Description of the illustration physical_attributes_clause.gif

(storage_clause::=)

table_compression::=

Description of table_compression.gif follows
Description of the illustration table_compression.gif

table_properties::=

Description of table_properties.gif follows
Description of the illustration table_properties.gif

(column_properties::=, table_partitioning_clauses ::=, parallel_clause ::=, enable_disable_clause ::=, row_movement_clause ::=, subquery::=)

column_properties::=

Description of column_properties.gif follows
Description of the illustration column_properties.gif

(object_type_col_properties::=, nested_table_col_properties ::=, varray_col_properties ::=, LOB_storage_clause::=, LOB_partition_storage::=, XMLType_column_properties ::=)

object_type_col_properties::=

Description of object_type_col_properties.gif follows
Description of the illustration object_type_col_properties.gif

substitutable_column_clause::=

Description of substitutable_column_clause.gif follows
Description of the illustration substitutable_column_clause.gif

nested_table_col_properties ::=

Description of nested_table_col_properties.gif follows
Description of the illustration nested_table_col_properties.gif

(substitutable_column_clause::=, object_properties::=, physical_properties::=, column_properties::=)

varray_col_properties ::=

Description of varray_col_properties.gif follows
Description of the illustration varray_col_properties.gif

(substitutable_column_clause::=, LOB_parameters ::=)

LOB_storage_clause::=

Description of LOB_storage_clause.gif follows
Description of the illustration LOB_storage_clause.gif

(LOB_parameters ::=)

LOB_parameters ::=

Description of LOB_parameters.gif follows
Description of the illustration LOB_parameters.gif

(storage_clause::=)

logging_clause::=

Description of logging_clause.gif follows
Description of the illustration logging_clause.gif

LOB_partition_storage::=

Description of LOB_partition_storage.gif follows
Description of the illustration LOB_partition_storage.gif

(LOB_storage_clause::=, varray_col_properties ::=)

XMLType_column_properties ::=

Description of XMLType_column_properties.gif follows
Description of the illustration XMLType_column_properties.gif

(XMLType_storage::=, XMLSchema_spec::=)

XMLType_storage::=

Description of XMLType_storage.gif follows
Description of the illustration XMLType_storage.gif

(LOB_parameters ::=)

XMLSchema_spec::=

Description of XMLSchema_spec.gif follows
Description of the illustration XMLSchema_spec.gif

row_movement_clause ::=

Description of row_movement_clause.gif follows
Description of the illustration row_movement_clause.gif

index_org_table_clause ::=

Description of index_org_table_clause.gif follows
Description of the illustration index_org_table_clause.gif

(mapping_table_clauses::=, key_compression ::=, index_org_overflow_clause ::=)

mapping_table_clauses::=

Description of mapping_table_clauses.gif follows
Description of the illustration mapping_table_clauses.gif

key_compression ::=

Description of key_compression.gif follows
Description of the illustration key_compression.gif

index_org_overflow_clause ::=

Description of index_org_overflow_clause.gif follows
Description of the illustration index_org_overflow_clause.gif

(segment_attributes_clause ::=)

supplemental_logging_props ::=

Description of supplemental_logging_props.gif follows
Description of the illustration supplemental_logging_props.gif

supplemental_log_grp_clause::=

Description of supplemental_log_grp_clause.gif follows
Description of the illustration supplemental_log_grp_clause.gif

supplemental_id_key_clause::=

Description of supplemental_id_key_clause.gif follows
Description of the illustration supplemental_id_key_clause.gif

external_table_clause ::=

Description of external_table_clause.gif follows
Description of the illustration external_table_clause.gif

(external_data_properties::=)

external_data_properties::=

Description of external_data_properties.gif follows
Description of the illustration external_data_properties.gif

(opaque_format_spec: See Oracle Database Utilities for information on how to specify values for the opaque_format_spec.)

table_partitioning_clauses ::=

Description of table_partitioning_clauses.gif follows
Description of the illustration table_partitioning_clauses.gif

(range_partitioning::=, hash_partitioning ::=, list_partitioning ::=, composite_partitioning ::=)

range_partitioning::=

Description of range_partitioning.gif follows
Description of the illustration range_partitioning.gif

(range_values_clause::=, table_partition_description::=)

hash_partitioning ::=

Description of hash_partitioning.gif follows
Description of the illustration hash_partitioning.gif

(individual_hash_partitions::=, hash_partitions_by_quantity::=)

list_partitioning ::=

Description of list_partitioning.gif follows
Description of the illustration list_partitioning.gif

(list_values_clause::=, table_partition_description::=)

composite_partitioning ::=

Description of composite_partitioning.gif follows
Description of the illustration composite_partitioning.gif

(subpartition_by_list::=, subpartition_by_hash::=, range_values_clause::=, table_partition_description::=)

subpartition_by_hash::=

Description of subpartition_by_hash.gif follows
Description of the illustration subpartition_by_hash.gif

(subpartition_template::=)

individual_hash_partitions::=

Description of individual_hash_partitions.gif follows
Description of the illustration individual_hash_partitions.gif

(partitioning_storage_clause::=)

hash_partitions_by_quantity::=

Description of hash_partitions_by_quantity.gif follows
Description of the illustration hash_partitions_by_quantity.gif

subpartition_by_list::=

Description of subpartition_by_list.gif follows
Description of the illustration subpartition_by_list.gif

(subpartition_template::=)

subpartition_template::=

Description of subpartition_template.gif follows
Description of the illustration subpartition_template.gif

(list_values_clause::=, partitioning_storage_clause::=)

range_values_clause::=

Description of range_values_clause.gif follows
Description of the illustration range_values_clause.gif

list_values_clause::=

Description of list_values_clause.gif follows
Description of the illustration list_values_clause.gif

table_partition_description::=

Description of table_partition_description.gif follows
Description of the illustration table_partition_description.gif

(segment_attributes_clause ::=, table_compression::=, LOB_storage_clause::=, varray_col_properties ::=, partition_level_subpartition::=)

partition_level_subpartition::=

Description of partition_level_subpartition.gif follows
Description of the illustration partition_level_subpartition.gif

(subpartition_spec::=)

subpartition_spec::=

Description of subpartition_spec.gif follows
Description of the illustration subpartition_spec.gif

(list_values_clause::=, partitioning_storage_clause::=)

partitioning_storage_clause::=

Description of partitioning_storage_clause.gif follows
Description of the illustration partitioning_storage_clause.gif

parallel_clause ::=

Description of parallel_clause.gif follows
Description of the illustration parallel_clause.gif

enable_disable_clause ::=

Description of enable_disable_clause.gif follows
Description of the illustration enable_disable_clause.gif

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

using_index_clause ::=

Description of using_index_clause.gif follows
Description of the illustration using_index_clause.gif

(create_index::=, index_properties::=)

index_properties::=

Description of index_properties.gif follows
Description of the illustration index_properties.gif

(global_partitioned_index ::=, local_partitioned_index ::=--part of CREATE INDEX, index_attributes::=, domain_index_clause: not supported in using_index_clause)

index_attributes::=

Description of index_attributes.gif follows
Description of the illustration index_attributes.gif

(physical_attributes_clause::=, logging_clause::=, key_compression ::=, parallel_clause: not supported in using_index_clause)

Semantics

relational_table

GLOBAL TEMPORARY

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

When you first create a temporary table, its table metadata is stored in the data dictionary, but no space is allocated for table data. Space is allocated for the table segment at the time of the first DML operation on the table. The temporary table definition persists in the same way as the definitions of regular tables, but the table segment and any data the table contains are either session-specific or transaction-specific data. You specify whether the table segment and data are session- or transaction-specific with the ON COMMIT keywords.

You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.


See Also:

Oracle Database Concepts for information on temporary tables and "Temporary Table Example"

Restrictions on Temporary Tables Temporary tables are subject to the following restrictions:

schema

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

table

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


See Also:

"General Examples"

relational_properties

The relational properties describe the components of a relational table.

column_definition

The column_definition lets you define the characteristics of the column.

column

Specify the name of a column of the table.

If you also specify AS subquery, then you can omit column and datatype unless you are creating an index-organized table. If you specify AS subquery when creating an index-organized table, then you must specify column, and you must omit datatype.

The absolute maximum number of columns in a table is 1000. However, when you create an object table or a relational table with columns of object, nested table, varray, or REF type, Oracle Database maps the columns of the user-defined types to relational columns, in effect creating hidden columns that count toward the 1000-column limit.

datatype

Specify the datatype of a column.

Notes on Table Column Datatypes The following notes apply to the datatypes of table columns:

Restriction on Table Column Datatypes You can specify a column of type ROWID, but Oracle Database does not guarantee that the values in such columns are valid rowids.


See Also:

"Datatypes" for information on LONG columns and on Oracle-supplied datatypes

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.

This clause instructs the database to sort the rows of the cluster on this column before applying the hash function. Doing so may improve response time during subsequent operations on the clustered data.


See Also:

"CLUSTER Clause" for information on creating a cluster table

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.

The DEFAULT expression can include any SQL function as long as the function does not return a literal argument, a column reference, or a nested function invocation.

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.


See Also:

"About SQL Expressions" for the syntax of expr

ENCRYPT encryption_spec

The ENCRYPT clause lets you use the transparent data encryption feature to encrypt the column you are defining. You can encrypt columns of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, DATE, and RAW. The data does not appear in its encrypted form to authorized users, such as the user who encrypts the column.


Note:

Column encryption requires that a system administrator with appropriate privileges has initialized the security module, opened a wallet, and set an encryption key. Please refer to Oracle Advanced Security Administrator's Guide for general information on encryption and to alter_system_security_clauses for related ALTER SYSTEM statements.

USING 'encrypt_algorithmUse this clause to specify the name of the algorithm to be used. Valid algorithms are 3DES168, AES128, AES192, and AES256. If you omit this clause, the database uses AES192. If you encrypt more than one column in the same table, and if you specify the USING clause for one of the columns, you must specify the same encryption algorithm for all the encrypted columns.

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

SALT | NO SALT By default the database appends a random string, called "salt", to the clear text of the column before encrypting it. If you want to use the column as an index key, you must specify NO SALT. Please refer to Oracle Advanced Security Administrator's Guide for a description of "salt" in this context.

Restrictions on the ENCRYPT clause: The following restrictions apply to column encryption:


See Also:

Oracle Advanced Security Administrator's Guide for more information about transparent data encryption

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. Please refer to constraint for syntax and description of these constraints as well as examples.

inline_ref_constraint and out_of_line_ref_constraint  These clauses let you describe a column of type REF. The only difference between these clauses is that you specify out_of_line_ref_constraint from the table level, so you must identify the REF column or attribute you are defining. Specify inline_ref_constraint as part of the definition of the REF column or attribute.

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.

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 Database will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS Specify PRESERVE ROWS for a session-specific temporary table. Oracle Database will truncate the table (delete all its rows) when you terminate the session.

physical_properties

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

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.


See Also:

CREATE TABLESPACE for more information on tablespaces

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

Please 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. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.

When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table:

Restrictions on Table Compression Table compression is subject to the following restrictions:

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.

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, the default is 50.

Restriction on PCTTHRESHOLD You cannot specify PCTTHRESHOLD for individual partitions of an index-organized table.

mapping_table_clauses Specify MAPPING TABLE to instruct the database to create a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table. If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and physical attributes as the base table partitions.

Oracle Database creates the mapping table or mapping table partition in the same tablespace as its parent index-organized table or partition. You cannot query, perform DML operations on, or modify the storage characteristics of the mapping table or its partitions.

key_compression  The key_compression clauses let you enable or disable key compression for index-organized tables.

Restriction on Key Compression of Index-organized Tables At the partition level, you can specify COMPRESS, but you cannot specify the prefix length with integer.

index_org_overflow_clause  The index_org_overflow_clause lets you instruct the database that index-organized table data rows exceeding the specified threshold are placed in the data segment specified in this clause.

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_table_clause

Use the external_table_clause to create an external table, which is a read-only table whose metadata is stored in the database but whose data in stored outside the database. Among other capabilities, external tables let you query data without first loading it into the database.


See Also:

Oracle Data Warehousing Guide, Oracle Database Administrator's Guide, and Oracle Database Utilities for information on the uses for external tables

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

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 two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. If you do not specify TYPE, then the database uses ORACLE_LOADER as the default access driver. You must specify the ORACLE_DATAPUMP access driver if you specify the AS subquery clause to unload data from one Oracle Database and reload it into the same or a different Oracle Database.


See Also:

Oracle Database Utilities for information about the ORACLE_LOADER and ORACLE_DATAPUMP access drivers

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.

LOCATION The LOCATION clause lets you specify one or more external data sources. Usually the location_specifier is a file, but it need not be. Oracle Database does not interpret this clause. It is up to the access driver to interpret this information in the context of the external data. You cannot use wildcards in the location_specifier to specify multiple files.

REJECT LIMIT The REJECT LIMIT clause lets you specify how many conversion errors can occur during a query of the external data before an Oracle Database error is returned and the query is aborted. The default value is 0.

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. Please refer to CREATE CLUSTER for more information.

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

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

Restrictions on Cluster Tables Cluster tables are subject to the following restrictions:

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.

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:

LOB_item

Specify the LOB column name or LOB object attribute for which you are explicitly defining tablespace and storage characteristics that are different from those of the table. Oracle Database automatically creates a system-managed index for each LOB_item you create.

LOB_segname

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

LOB_parameters

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

ENABLE STORAGE IN ROW  If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information. This is the default.

Restriction on Enabling Storage in Row For an index-organized table, you cannot specify this parameter unless you have specified an OVERFLOW segment in the index_org_table_clause.

DISABLE STORAGE IN ROW  If you disable storage in row, then the LOB value is stored outside of the row out of line regardless of the length of the LOB value.

The LOB locator is always stored inline regardless of where the LOB value is stored. You cannot change the value of STORAGE IN ROW once it is set except by moving the table. See the move_table_clause in the ALTER TABLE documentation for more information.

CHUNK integer  Specify the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, then the database rounds up in bytes to the next multiple. For example, if the database block size is 2048 and integer is 2050, then the database allocates 4096 bytes (2 blocks). The maximum value is 32768 (32K), which is the largest Oracle Database block size allowed. The default CHUNK size is one Oracle Database block.

The value of CHUNK must be less than or equal to the value of NEXT, either the default value or that specified in the storage_clause. If CHUNK exceeds the value of NEXT, then the database returns an error.You cannot change the value of CHUNK once it is set.

PCTVERSION integer  Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.

You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode. You cannot specify both PCTVERSION and RETENTION.

RETENTION  Use this clause to indicate that Oracle Database should retain old versions of this LOB column. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database.

You can specify the RETENTION parameter only if the database is running in automatic undo mode. In this mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION.

FREEPOOLS integer  Specify the number of groups of free lists for the LOB segment. Normally integer will be the number of instances in a Real Application Clusters environment or 1 for a single-instance database.

You can specify this parameter only if the database is running in automatic undo mode. In this mode, FREEPOOLS is the default unless you specify the FREELIST GROUPS parameter of the storage_clause. If you specify neither FREEPOOLS nor FREELIST GROUPS, then the database uses a default of FREEPOOLS 1 if the database is in automatic undo management mode and a default of FREELIST GROUPS 1 if the database is in manual undo management mode.

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

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 LOB Clause If you specify STORE AS LOB:

If you do not specify STORE AS LOB, then storage is based on the maximum possible size of the varray rather than on the actual size of a varray column. The maximum size of the varray is the number of elements times the element size, plus a small amount for system control information. If you omit this clause:

Restriction on Storing Varrays as LOBs You cannot specify the TABLESPACE parameter of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the tablespace of the containing table or table partition.

substitutable_column_clause The substitutable_column_clause has the same behavior as described for object_type_col_properties.

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.


See Also:

"Multi-level Collection Example" for examples using nested_item and COLUMN_VALUE

storage_table Specify the name of the table where the rows of nested_item reside.

You cannot query or perform DML statements on storage_table directly, but you can modify its storage characteristics by specifying its name in an ALTER TABLE statement.

Restriction on the Storage Table You cannot partition the storage table of a nested table.


See Also:

ALTER TABLE for information about modifying nested table column storage 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 columns can be stored either in LOB or object-relational columns.

XMLSchema_spec This clause lets you specify the URL of a registered XMLSchema, either in the XMLSCHEMA clause or as part of the ELEMENT clause, and an XML element name. You must specify an element, although the XMLSchema URL is optional. If you do specify an XMLSchema URL, then you must already have registered the XMLSchema using the DBMS_XMLSCHEMA package.


See Also:


table_partitioning_clauses

Use the table_partitioning_clauses to create a partitioned table.

Restrictions on Partitioning in General All partitioning is subject to the following restrictions:

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator's Guide for a discussion of these restrictions.

range_partitioning

Use the range_partitioning 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.

column

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

Restriction on Partitioning Key Columns The columns in the column list can be of any built-in datatype except ROWID, LONG, LOB, or TIMESTAMP WITH TIME ZONE. However, columns of TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE can be used in the partitioning key.

PARTITION partition

The name partition must conform to the rules for naming schema objects and their part as described in "Schema Object Naming Rules". If you omit partition, then the database generates a name with the form SYS_Pn.

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_partitioning clause. You can substitute the keyword MAXVALUE for any literal in 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. Please refer to Oracle Database Globalization Support Guide for more information on these initialization parameters.


See Also:


table_partition_description

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

The segment_attributes_clause and table_compression clause have the same function as described for the table_properties of the table as a whole.

The key_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 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. The system-generated names for LOB data and LOB index partitions take the form SYS_LOB_Pn and SYS_IL_Pn, respectively. The corresponding system-generated names for LOB data and index subpartitions are SYS_LOB_SUBPn and SYS_IL_SUBPn, respectively

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

Restriction on table_partition_description The partition_level_subpartition clause is valid only for composite-partitioned tables. See partition_level_subpartition.

hash_partitioning

Use the hash_partitioning 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 subpartitions the database should create.

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. If you omit the partition name, then the database assigns partition names of the form SYS_Pn.

Restriction on Specifying Individual Hash Partitions The only clause you can specify in the partitioning_storage_clause is the TABLESPACE clause.


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. Please refer to Oracle Database Globalization Support Guide for more information on character set support.

hash_partitions_by_quantity  Alternatively, you can specify the number of partitions. In this case, the database assigns partition names of the form SYS_Pn. 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, 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. Also for both methods of hash partitioning, the only attribute you can specify for hash partitions is TABLESPACE. Hash partitions inherit all other attributes from 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.

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.


See Also:

Oracle Database Concepts for more information on hash partitioning

Restrictions on Hash Partitioning Hash partitioning is subject to the following restrictions:

list_partitioning

Use the list_partitioning clause to partition the table on lists of literal values from column. List partitioning is useful for controlling how individual rows map to specific partitions.

If you omit the partition name, then the database assigns partition names of the form SYS_Pn.

list_values_clause The list_values_clause of each partition must have at least one value. No value, including NULL, 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.

Restrictions on List Partitioning List partitioning is subject to the following restrictions:

composite_partitioning

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

After establishing the type of subpartitioning you want for each composite partition, using the subpartition_by_hash or subpartition_by_list clause, you must define each of the range partitions.

Restriction on Composite Partitioning You cannot specify composite partitioning for an index-organized table. Therefore, the OVERFLOW clause of the table_partition_description is not valid for composite-partitioned tables.

subpartition_template The subpartition_template is an optional element of both range-hash and range-list composite partitioning. The template lets you define default subpartitions for each table partition. Oracle Database will create these default subpartitions 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 partition_level_subpartition clause.

When defining subpartitions with a template, you must specify a name for each subpartition.


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:
  • Omit tablespace storage at the partition level and specify tablespace storage in the subpartition template.

  • Define individual subpartitions with specific tablespace storage.


Restrictions on Subpartition Templates Subpartition templates are subject to the following restrictions:

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 quantity clause. See subpartition_template. In either case, for optimal load balancing you should specify a number of partitions that is a power of 2.

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 omit both this clause and subpartition_template, then the database will create each partition with one hash subpartition unless you subsequently specify the partition_level_subpartition clause.

Restriction on Hash Subpartitioning In addition to the restrictions for composite partitioning in general (see composite_partitioning ), for hash subpartitioning in subpartition_template, you cannot specify the list_values_clause.

subpartition_by_list

Use the subpartition_by_list clause to indicate that the database should subpartition each partition in table by literal values from column.

If you omit subpartition_template, then you can define list subpartitions individually for each partition using the partition_level_subpartition clause of table_partition_description. If you omit both subpartition_template and partition_level_subpartition, then the database creates a single DEFAULT subpartition.

Restrictions on List Subpartitioning List subpartitioning is subject to the following restrictions:

In addition to the restrictions for composite partitioning in general (see composite_partitioning ), for list subpartitioning:

partition_level_subpartition

This clause of the table_partition_description is valid only for composite-partitioned tables. This clause lets you specify hash or list subpartitions for partition. This clause overrides the default settings established for range-hash composite partitions in the subpartition_by_hash clause or for range-hash or range-list composite partitions in the subpartition_template.

Notes on Composite Partitions The following notes apply to composite partitions:

For range-hash composite partitions, the list_values_clause of subpartition_spec is not relevant and is invalid.

For range-list composite partitions:

CACHE | NOCACHE | CACHE READS

Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE, then:

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.

As a parameter in the LOB_storage_clause, CACHE specifies that the database places LOB data values in the buffer cache for faster access.

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


See Also:

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

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


See Also:

parallel_clause for more information on this clause

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.

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


See Also:

Oracle Database Advanced Replication for information about the use of row-level dependency tracking in replication environments

MONITORING | NOMONITORING

In earlier releases, you could use these clauses to start or stop the collection of modification statistics on this table. These clauses have been deprecated.


See Also:

Oracle Database Reference for information on the STATISTICS_LEVEL initialization parameter, including its affect on Oracle Database manageability features

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:


See Also:

constraint for more information on constraints, "ENABLE VALIDATE Example", and "DISABLE Example"

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.

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. This clause is discussed fully in using_index_clause in the documentation on constraints.


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.


Caution:

If you need static rowids for data access, 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.

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


See Also:

Oracle Data Warehousing Guide for more information on materialized views and query rewrite

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

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

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.


See Also:

storage_clause for information on these parameters

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

XMLType_storage

This clause lets you determine how Oracle Database manages the storage of the underlying columns.

OBJECT RELATIONAL Specify OBJECT RELATIONAL if you want the database to store the XMLType data in object relational columns. If you specify OBJECT RELATIONAL, then you must also specify an XMLSchema in the XMLSchema_storage_clause, and you must already have registered the schema using the DBMS_XMLSCHEMA package. Oracle Database will create the table conforming to the registered schema.

CLOB Specify CLOB if you want the database to store the XML data in a CLOB column. If you specify CLOB, then you may also specify either a LOB segment name, or the LOB_parameters clause, or both.

XMLSchema_spec

This clause lets you specify the URL of a registered XMLSchema, either in the XMLSCHEMA clause or as part of the ELEMENT clause, and an XML element name. You must specify an element, although the XMLSchema URL is optional. If you do specify an XMLSchema URL, then you must already have registered the XMLSchema using the DBMS_XMLSCHEMA package.


See Also:


Examples

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 datatype NUMBER. The hire_date column is of datatype 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.

Storage Example To define the same employees_demo table in the example tablespace with a small storage capacity and limited allocation potential, 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     6144  
            NEXT        6144 
            MINEXTENTS     1  
            MAXEXTENTS     5 ); 

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;

Substitutable Table and Column Examples The following statement creates a substitutable table from the person_t type, which was created in "Type Hierarchy Example":

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.

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

NOPARALLEL Example 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;

ENABLE VALIDATE Example 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)
    ) ;

DISABLE Example 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;

Multi-level 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);

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
    , press_release     LONG
    ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_new
    LOB (ad_sourcetext, ad_finaltext) STORE AS
      (TABLESPACE example
       STORAGE (INITIAL 6144 NEXT 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 
      NEXT  2K 
      PCTINCREASE 0 
      MINEXTENTS 1 
      MAXEXTENTS 1 ) 
   OVERFLOW 
    STORAGE 
      ( INITIAL  4K 
        NEXT  2K 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 1 ); 

External Table Example The following statement creates an external table that represents a subset of the sample table hr.departments. The opaque_format_spec is shown in italics. Please refer to Oracle Database Utilities for information on the ORACLE_LOADER access driver and how to specify values for the opaque_format_spec.

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;

See Also:

"Creating a Directory: Examples" to see how the admin directory was created

XMLType Examples

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

XMLType Table Examples The following example creates a very simple XMLType table with one implicit CLOB column:

CREATE TABLE xwarehouses OF XMLTYPE;

Because Oracle Database implicitly stores the data in a CLOB column, it is subject to all of the restrictions on LOB columns. To avoid these restrictions, you can create an XMLSchema-based table, as shown in the following example. The XMLSchema must already have been created (see "Using XML in SQL Statements" for more information):

CREATE TABLE xwarehouses OF XMLTYPE
   XMLSCHEMA "http://www.oracle.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 NEXT 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.oracle.com/xwarehouses.xsd"
      ELEMENT "Warehouse";

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

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(30))
   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, but the LONG column press_release is omitted because LONG columns are not supported in partitioning.

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 (NEXT 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)),
       PARTITION P2 VALUES LESS THAN (MAXVALUE)
         LOB (ad_composite, ad_finaltext)
         STORE AS (TABLESPACE tbs_03)
       )
   TABLESPACE tbs_04;

Partition p1 will be in tablespace tbs_1. The LOB data partitions for ad_composite and ad_photo will be in tablespace tbs_2. The LOB data partition for the remaining LOB columns will be in tablespace tbs_1. 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_2 for columns ad_composite and ad_photo and from tablespace tbs_1 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.

Partition p2 will be in the default tablespace tbs_4. The LOB data for ad_composite and ad_finaltext will be in tablespace tbs_3. The LOB data for the remaining LOB columns will be in tablespace tbs_4. The LOB index for columns ad_composite and ad_finaltext will be in tablespace tbs_3. The LOB index for the remaining LOB columns will be in tablespace tbs_4.

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) 
    , 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 5 
 STORE IN (tbs_01, tbs_02, tbs_03, tbs_04); 

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_object_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') );