| Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
| Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Purpose
Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition. For object tables or relational tables with object columns, use ALTER TABLE to convert the table to the latest definition of its referenced type after the type has been altered.
See Also:
CREATE TABLE for information on creating tables
Oracle Text Reference for information on ALTER TABLE statements in conjunction with Oracle Text
Additional Topics:
Prerequisites
The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.
Additional Prerequisites for Partitioning Operations If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause.
You must also have space quota in the tablespace in which space is to be acquired in order to use the add_table_partition, modify_table_partition, move_table_partition, and split_table_partition clauses.
Additional Prerequisites for Constraints and Triggers 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 enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.
Additional Prerequisites When Using Object Types To use an object type in a column definition when modifying a table, either that object must belong to the same schema as the table being altered, or you must have either the EXECUTE ANY TYPE system privilege or the EXECUTE schema object privilege for the object type.
See Also:
CREATE INDEX for information on the privileges needed to create indexesSyntax
alter_table::=
Note:
You must specify some clause after
table. That is, none of the clauses after table are required, but you must specify at least one of them.Groups of ALTER TABLE syntax:
After each clause you will find links to its component subclauses.
(physical_attributes_clause::=, logging_clause::=, table_compression ::=, supplemental_table_logging ::=, allocate_extent_clause ::=, deallocate_unused_clause::= , shrink_clause::=, upgrade_table_clause ::=, records_per_block_clause ::=, parallel_clause::=, row_movement_clause::=, alter_iot_clauses::=)
supplemental_table_logging ::=
supplemental_log_grp_clause::=
supplemental_id_key_clause::=
parallel_clause::=
(alter_overflow_clause ::=, alter_mapping_table_clauses ::=)
mapping_table_clauses::=
key_compression::=
index_org_overflow_clause::=
(segment_attributes_clause::=)
segment_attributes_clause::=
(physical_attributes_clause::=, logging_clause::=)
(segment_attributes_clause::=, allocate_extent_clause ::=, shrink_clause::=, deallocate_unused_clause::=)
(segment_attributes_clause::=)
alter_mapping_table_clauses ::=
(allocate_extent_clause ::=, deallocate_unused_clause::=)
(add_column_clause ::=, modify_column_clauses::=, drop_column_clause ::=, rename_column_clause ::=, modify_collection_retrieval ::=, modify_LOB_storage_clause::=, alter_varray_col_properties::=, encryption_spec::=)
(column_definition::=, column_properties::=)
(encryption_spec::=, inline_constraint and inline_ref_constraint: constraint::=)
(encryption_spec::=, inline_constraint: constraint::=, LOB_storage_clause::=)
encryption_spec::=
modify_collection_retrieval ::=
(constraint_state: constraint::=)
substitutable_column_clause::=
nested_table_col_properties::=
object_properties::=
(inline_constraint, inline_ref_constraint, out_of_line_constraint, out_of_line_ref_constraint: constraint::=)
supplemental_logging_props::=
(supplemental_log_grp_clause::=, supplemental_id_key_clause::=)
physical_properties::=
(segment_attributes_clause::=, index_org_table_clause ::=, external_data_properties::=)
(substitutable_column_clause::=)
LOB_parameters::=
(storage_clause::=, logging_clause::=)
modify_LOB_parameters::=
(storage_clause::=, logging_clause::=, allocate_extent_clause ::=, shrink_clause::=, deallocate_unused_clause::=)
alter_varray_col_properties::=
(LOB_storage_clause::=, varray_col_properties::=)
XMLType_storage::=
XMLSchema_spec::=
alter_external_table_clauses ::=
(add_column_clause ::=, modify_column_clauses::=, drop_column_clause ::=, drop_constraint_clause::=, parallel_clause::=)
external_data_properties::=
(modify_table_default_attrs ::=, set_subpartition_template ::=, modify_table_partition::=, modify_table_subpartition::=, move_table_partition ::=, move_table_subpartition::=, add_table_partition::=, coalesce_table_partition::=, drop_table_partition::=, drop_table_subpartition ::=, rename_partition_subpart::=, truncate_partition_subpart::=, split_table_partition::=, split_table_subpartition ::=, merge_table_partitions ::=, merge_table_subpartitions ::=, exchange_partition_subpart ::=
modify_table_default_attrs ::=
(segment_attributes_clause::=, table_compression ::=, key_compression::=, LOB_parameters::=, alter_overflow_clause ::=)
(list_values_clause::=, partitioning_storage_clause::=)
(modify_range_partition::=, modify_hash_partition::=, modify_list_partition::=)
modify_range_partition::=
(partition_attributes::=, update_index_clauses::=, alter_mapping_table_clauses ::=)
(partition_attributes::=, add_hash_subpartition ::=, parallel_clause, alter_mapping_table_clauses ::=)
modify_list_partition::=
(partition_attributes::=, add_list_subpartition::=)
modify_table_subpartition::=
(modify_hash_subpartition::=, modify_list_subpartition ::=)
(table_partition_description::=, update_index_clauses::=, parallel_clause::=)
(subpartition_spec::=, update_index_clauses::=, parallel_clause::=)
add_table_partition::=
(range_values_clause::=, table_partition_description::=, update_index_clauses::=)
(partitioning_storage_clause::=, update_index_clauses::=, parallel_clause::=)
(list_values_clause::=, table_partition_description::=, update_index_clauses::=)
(update_index_clauses::=, parallel_clause::=)
(update_index_clauses::=, parallel_clause::=)
(update_index_clauses::=, parallel_clause::=)
(update_index_clauses::=, parallel_clause::=)
(partition_spec::=, update_index_clauses::=, parallel_clause::=)
(subpartition_spec::=, update_index_clauses::=, parallel_clause::=)
(partition_spec::=, update_index_clauses::=, parallel_clause::=)
(subpartition_spec::=, parallel_clause::=)
exchange_partition_subpart ::=
(update_index_clauses::=, parallel_clause::=)
list_values_clause::=
range_values_clause::=
partitioning_storage_clause::=
partition_attributes::=
(physical_attributes_clause::=, logging_clause::=, allocate_extent_clause ::=, deallocate_unused_clause::=, shrink_clause::=, table_compression ::=, modify_LOB_parameters::=)
(subpartition_spec::=, update_index_clauses::=, parallel_clause::=)
(subpartition_spec::=, update_index_clauses::=)
(allocate_extent_clause ::=, deallocate_unused_clause::=, shrink_clause::=, modify_LOB_parameters::=)
(allocate_extent_clause ::=, deallocate_unused_clause::=, shrink_clause::=, modify_LOB_parameters::=)
table_partition_description::=
(segment_attributes_clause::=, table_compression ::=, key_compression::=, LOB_storage_clause::=, varray_col_properties::=)
partition_level_subpartition::=
partition_spec::=
(table_partition_description::=)
subpartition_spec::=
(list_values_clause::=, partitioning_storage_clause::=)
(update_global_index_clause ::=, update_all_indexes_clause::=)
update_global_index_clause ::=
update_index_partition::=
update_index_subpartition::=
index_partition_description::=
index_subpartition_clause::=
parallel_clause ::=
(segment_attributes_clause::=, table_compression ::=, index_org_table_clause ::=, LOB_storage_clause::=, varray_col_properties::=)
(using_index_clause::=, exceptions_clause::=,)
(create_index::=, index_properties::=)
index_properties::=
(global_partitioned_index ::=, local_partitioned_index ::=--part of CREATE INDEX, index_attributes::=, domain_index_clause: not supported in using_index_clause)
index_attributes::=
(physical_attributes_clause::=, logging_clause::=, key_compression::=, parallel_clause: not supported in using_index_clause)
Semantics
Many clauses of the ALTER TABLE statement have the same functionality they have in a CREATE TABLE statement. For more information on such clauses, please see CREATE TABLE.
Note:
Operations performed by theALTER TABLE statement can cause Oracle Database to invalidate procedures and stored functions that access the table. For information on how and when the database invalidates such objects, see Oracle Database Concepts.schema
Specify the schema containing the table. If you omit schema, then Oracle Database assumes the table is in your own schema.
table
Specify the name of the table to be altered.
Note:
If you alter a table that is a master table for one or more materialized views, then Oracle Database marks the materialized viewsINVALID. Invalid materialized views cannot be used by query rewrite and cannot be refreshed. For information on revalidating a materialized view, see ALTER MATERIALIZED VIEW.See Also:
Oracle Data Warehousing Guide for more information on materialized views in generalRestrictions on Altering Temporary Tables You can modify, drop columns from, or rename a temporary table. However, for a temporary table you cannot:
Add columns of nested table type. You can add columns of other types.
Specify referential integrity (foreign key) constraints for an added or modified column.
Specify the following clauses of the LOB_storage_clause for an added or modified LOB column: TABLESPACE, storage_clause, logging_clause, allocate_extent_clause, or deallocate_unused_clause.
Specify the physical_attributes_clause, nested_table_col_properties, parallel_clause, allocate_extent_clause, deallocate_unused_clause, or any of the index-organized table clauses.
Exchange partitions between a partition and a temporary table.
Specify the logging_clause.
Specify MOVE.
Restrictions on Altering External Tables You can add, drop, or modify the columns of an external table. However, for an external table you cannot:
Add a LONG, LOB, or object type column or change the datatype of an external table column to any of these datatypes.
Add a constraint to an external table.
Modify the storage parameters of an external table.
Specify the logging_clause.
Specify MOVE.
alter_table_properties
Use the alter_table_clauses to modify a database table.
The physical_attributes_clause lets you change the value of the PCTFREE, PCTUSED, and INITRANS parameters and storage characteristics. Please refer to physical_attributes_clause and storage_clause for a full description of these parameters and characteristics.
Restrictions on Altering Table Physical Attributes Altering physical attributes is subject to the following restrictions:
You cannot specify the PCTUSED parameter for the index segment of an index-organized table.
If you attempt to alter the storage attributes of tables in locally managed tablespaces, then Oracle Database raises an error. However, if some segments of a partitioned table reside in a locally managed tablespace and other segments reside in a dictionary-managed tablespace, then the database alters the storage attributes of the segments in the dictionary-managed tablespace but does not alter the attributes of the segments in the locally managed tablespace, and does not raise an error.
For segments with automatic segment-space management, the database ignores attempts to change the PCTUSED setting. If you alter the PCTFREE setting, then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment.
Cautions on Altering Tables Physical Attributes The values you specify in this clause affect the table as follows:
For a nonpartitioned table, the values you specify override any values specified for the table at create time.
For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_table_default_attrs clause.
For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_table_default_attrs clause with the FOR PARTITION clause.
Use the logging_clause to change the logging attribute of the table. The logging_clause specifies whether subsequent ALTER TABLE ... MOVE and ALTER TABLE ... SPLIT operations will be logged or not logged.
When used with the modify_table_default_attrs clause, this clause affects the logging attribute of a partitioned table.
See Also:
logging_clause for a full description of this clause
Oracle Data Warehousing Guide for more information about the logging_clause and parallel DML
The table_compression clause is valid only for heap-organized tables. Use this clause to instruct Oracle Database whether to compress data segments to reduce disk and memory use. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression.
Note:
The first time a table is altered in such a way that compressed data will be added, all bitmap indexes and bitmap index partitions on that table must be markedUNUSABLE.See Also:
Oracle Database Performance Tuning Guide for information on calculating the compression ratio and to Oracle Data Warehousing Guide for information on table compression usage scenarios
table_compression clause of CREATE TABLE information on creating objects with table compression
Use the supplemental_table_logging clause to add or drop a redo log group or one or more supplementally logged columns in a redo log group.
In the ADD clause, use supplemental_log_grp_clause to create named supplemental log group. Use the supplemental_id_key_clause to create a system-generated log group.
On the DROP clause, use GROUP log_group syntax to drop a named supplemental log group and use the supplemental_id_key_clause to drop a system-generated log group.
The supplemental_log_grp_clause and the supplemental_id_key_clause have the same semantics in CREATE TABLE and ALTER TABLE statements. For full information on these clauses, please refer to supplemental_log_grp_clause and supplemental_id_key_clause in the documentation on CREATE TABLE.
See Also:
Oracle Data Guard Concepts and Administration for information on supplemental redo log groupsUse the allocate_extent_clause to explicitly allocate a new extent for the table, the partition or subpartition, the overflow data segment, the LOB data segment, or the LOB index.
Restriction on Allocating Table Extents You cannot allocate an extent for a temporary table or for a range- or composite-partitioned table.
See Also:
allocate_extent_clause for a full description of this clause and "Allocating Extents: Example"Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and make the space available for other segments in the tablespace.
See Also:
deallocate_unused_clause for a full description of this clause and "Deallocating Unused Space: Example"The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.
Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.
COMPACT If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.
For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate COALESCE clause.
CASCADE If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.
Restrictions on the shrink_clause The shrink_clause is subject to the following restrictions:
You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
You cannot specify this clause for a compressed table.
You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
CACHE | NOCACHE
The CACHE and NOCACHE clauses have the same semantics in CREATE TABLE and ALTER TABLE statements. For complete information on these clauses, please refer to "CACHE | NOCACHE | CACHE READS" in the documentation on CREATE TABLE. If you omit both of these clauses in an ALTER TABLE statement, then the existing value is unchanged.
The upgrade_table_clause is relevant for object tables and for relational tables with object columns. It lets you instruct Oracle Database to convert the metadata of the target table to conform with the latest version of each referenced type. If table is already valid, then the table metadata remains unchanged.
Restriction on Upgrading Object Tables and Columns Within this clause, you cannot specify object_type_col_properties as a clause of column_properties.
INCLUDING DATA Specify INCLUDING DATA if you want Oracle Database to convert the data in the table to the latest type version format. You can define the storage for any new column while upgrading the table by using the column_properties and the LOB_partition_storage . This is the default.
You can convert data in the table at the time you upgrade the type by specifying CASCADE INCLUDING TABLE DATA in the dependent_handling_clause of the ALTER TYPE statement. See dependent_handling_clause . For information on whether a table contains data based on an older type version, refer to the DATA_UPGRADED column of the USER_TAB_COLUMNS data dictionary view.
NOT INCLUDING DATA Specify NOT INCLUDING DATA if you want Oracle Database to leave column data unchanged.
Restriction on NOT INCLUDING DATA You cannot specify NOT INCLUDING DATA if the table contains columns in Oracle8 release 8.0.x image format. To determine whether the table contains such columns, refer to the V80_FMT_IMAGE column of the USER_TAB_COLUMNS data dictionary view.
See Also:
Oracle Database Reference for information on the data dictionary views
ALTER TYPE for information on converting dependent table data when modifying a type upon which the table depends
Oracle Database Application Developer's Guide - Object-Relational Features for more information on the implications of not converting table data to the latest type version format
The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.
Restrictions on Records in a Block The record_per_block_clause is subject to the following restrictions:
You cannot specify either MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index.
You cannot specify this clause for an index-organized table or a nested table.
MINIMIZE Specify MINIMIZE to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records.
Oracle recommends that a representative set of data already exist in the table before you specify MINIMIZE. If you are using table compression (see table_compression ), then a representative set of compressed data should already exist in the table.
Restriction on MINIMIZE You cannot specify MINIMIZE for an empty table.
NOMINIMIZE Specify NOMINIMIZE to disable the MINIMIZE feature. This is the default.
This clause has the same semantics in CREATE TABLE and ALTER TABLE statements. For complete information on these clauses, please refer to row_movement_clause in the documentation on CREATE TABLE.
Use the RENAME clause to rename table to new_table_name.
Note:
Using this clause invalidates any dependent materialized views. For more information on materialized views, see CREATE MATERIALIZED VIEW and Oracle Data Warehousing Guide.alter_iot_clauses
This clause lets you change a table that is not index organized into an index-organized table. Index-organized tables keep data sorted on the primary key and are therefore best suited for primary-key-based access and manipulation.See index_org_table_clause in the context of CREATE TABLE.
This clause is relevant only if table is index organized. Specify COMPRESS to instruct Oracle Database to combine the primary key index blocks of the index-organized table where possible to free blocks for reuse. You can specify this clause with the parallel_clause.
PCTTHRESHOLD integer Please refer to "PCTTHRESHOLD integer" in the documentation of CREATE TABLE.
INCLUDING column_name Please refer to "INCLUDING column_name" in the documentation of CREATE TABLE.
The overflow_attributes let you specify the overflow data segment physical storage and logging attributes to be modified for the index-organized table. Parameter values specified in this clause apply only to the overflow data segment.
See Also:
CREATE TABLEThe add_overflow_clause lets you add an overflow data segment to the specified index-organized table. You can also use this clause to explicitly allocate an extent to or deallocate unused space from an existing overflow segment.
Use the STORE IN tablespace clause to specify tablespace storage for the entire overflow segment. Use the PARTITION clause to specify tablespace storage for the segment by partition.
For a partitioned index-organized table:
If you do not specify PARTITION, then Oracle Database automatically allocates an overflow segment for each partition. The physical attributes of these segments are inherited from the table level.
If you want to specify separate physical attributes for one or more partitions, then you must specify such attributes for every partition in the table. You need not specify the name of the partitions, but you must specify their attributes in the order in which they were created.
You can find the order of the partitions by querying the PARTITION_NAME and PARTITION_POSITION columns of the USER_IND_PARTITIONS view.
If you do not specify TABLESPACE for a particular partition, then the database uses the tablespace specified for the table. If you do not specify TABLESPACE at the table level, then the database uses the tablespace of the partition primary key index segment.
Restrictions on Overflow Attributes Within the segment_attributes_clause:
You cannot specify the OPTIMAL parameter of the physical_attributes_clause.
You cannot specify tablespace storage for the overflow segment using this clause. For a nonpartitioned table, you can use ALTER TABLE ... MOVE ... OVERFLOW to move the segment to a different tablespace. For a partitioned table, use ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES ... OVERFLOW to change the default tablespace of the overflow segment.
Additional restrictions apply if table is in a locally managed tablespace, because in such tablespaces several segment attributes are managed automatically by the database.
See Also:
allocate_extent_clause and deallocate_unused_clause for full descriptions of these clauses of theadd_overflow_clauseThe alter_overflow_clause lets you change the definition of the overflow segment of an existing index-organized table.
The restrictions that apply to the add_overflow_clause also apply to the alter_overflow_clause.
Note:
When you add a column to an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specifiedOVERFLOW, then the database raises an error and does not execute the ALTER TABLE statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.The alter_mapping_table_clauses is valid only if table is index organized and has a mapping table.
allocate_extent_clause Use the allocate_extent_clause to allocate a new extent at the end of the mapping table for the index-organized table. Please refer to allocate_extent_clause for a full description of this clause.
deallocate_unused_clause Specify the deallocate_unused_clause to deallocate unused space at the end of the mapping table of the index-organized table. Please refer to deallocate_unused_clause for a full description of this clause.
Oracle Database automatically maintains all other attributes of the mapping table or its partitions.
Specify COALESCE to instruct Oracle Database to merge the contents of index blocks of the index the database uses to maintain the index-organized table where possible to free blocks for reuse. Please refer to the shrink_clause for information on the relationship between these two clauses.
column_clauses
Use these clauses to add, drop, or otherwise modify a column.
The add_column_clause lets you add a column to a table.
See Also:
CREATE TABLE for a description of the keywords and parameters of this clause and "Adding a Table Column: Example"column_definition
Unless otherwise noted in this section, the elements of column_definition have the same behavior when adding a column to an existing table as they do when creating a new table. Please refer to column_definition for information.
Restriction on column_definition The SORT parameter is valid only when creating a new table. You cannot specify SORT in the column_definition of an ALTER TABLE ... ADD statement.
If you add a column, then the initial value of each row for the new column is null unless you specify the DEFAULT clause. In this case, Oracle Database updates each row in the new column with the value you specify for DEFAULT. This update operation, in turn, fires any AFTER UPDATE triggers defined on the table.
Note:
If a column has a default value, then you can use theDEFAULT clause to change the default to NULL, but you cannot remove the default value completely. That is, if a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL.You can add an overflow data segment to each partition of a partitioned index-organized table.
You can add LOB columns to nonpartitioned and partitioned tables. You can specify LOB storage at the table and at the partition or subpartition level.
If you previously created a view with a query that used the SELECT * syntax to select all columns from table, and you now add a column to table, then the database does not automatically add the new column to the view. To add the new column to the view, re-create the view using the CREATE VIEW statement with the OR REPLACE clause. Please refer to CREATE VIEW for more information.
Restrictions on Adding Columns The addition of columns is subject to the following restrictions:
You cannot add a LOB column to a cluster table.
If you add a LOB column to a hash-partitioned table, then the only attribute you can specify for the new partition is TABLESPACE.
You cannot add a column with a NOT NULL constraint if table has any rows unless you also specify the DEFAULT clause.
If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.
You cannot add a column with a default value to a compressed table or to a partitioned table containing any compressed partition, unless you first disable compression for the table or partition.
Use the DEFAULT clause to specify a default for a new column or a new default for an existing column. Oracle Database assigns this value to the column if a subsequent INSERT statement omits a value for the column. If you are adding a new column to the table and specify the default value, then the database inserts the default column value into all rows of the table.
The datatype of the default value must match the datatype specified for the column. The column must also be large enough to hold the default value.
Restrictions on Default Column Values Default column values are subject to the following restrictions:
A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
The expression can be of any form except a scalar subquery expression.
Use inline_constraint to add a constraint to the new column.
This clause lets you describe a new column of type REF. Please refer to constraint for syntax and description of this type of constraint, including restrictions.
The clauses of column_properties determine the storage characteristics of an object type, nested table, varray, or LOB column.
object_type_col_properties This clause is valid only when you are adding a new object type column or attribute. To modify the properties of an existing object type column, use the modify_column_clauses. The semantics of this clause are the same as for CREATE TABLE unless otherwise noted.
Use the object_type_col_properties clause to specify storage characteristics for a new object column or attribute or an element of a collection column or attribute.
For complete information on this clause, please refer to object_type_col_properties in the documentation on CREATE TABLE.
nested_table_col_properties The nested_table_col_properties clause lets you specify separate storage characteristics for a nested table, which in turn lets you to define the nested table as an index-organized table. 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.)
For nested_item, specify the name of a column (or a top-level attribute of t