| Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
| Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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:
|
The table must be in your own schema, or you must have ALTER privilege on the table, or you must have ALTER ANY TABLE system privilege. For some operations you may also need the CREATE ANY INDEX privilege.
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.
To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table.
To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.
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 indexes |
alter_table::=
|
Note: You must specify some clause after |
Groups of ALTER TABLE syntax:
After each clause you will find links additional links to its component subclauses.
physical_attributes_clause::=, logging_clause::=, data_segment_compression::=, supplemental_lg_grp_clauses::=, allocate_extent_clause::=,deallocate_unused_clause::= , upgrade_table_clause::=, records_per_block_clause::=, parallel_clause::=, row_movement_clause::=, alter_iot_clauses::=)
supplemental_lg_grp_clauses::=
parallel_clause::=
mapping_table_clause::=
key_compression::=
index_org_overflow_clause::=
segment_attributes_clause::=
alter_mapping_table_clauses::=
inline_constraint and inline_ref_constraint: constraints, column_properties::=)
(inline_constraint: constraints)
modify_collection_retrieval::=
(constraint_state: constraints)
substitutable_column_clause::=
nested_table_col_properties::=
object_properties::=
inline_constraint, inline_ref_constraint, out_of_line_constraint, out_of_line_ref_constraint: constraints)supplemental_logging_props::=
physical_properties::=
LOB_parameters::=
modify_LOB_parameters::=
alter_varray_col_properties::=
XMLType_storage::=
XMLSchema_spec::=
alter_external_table_clause::=
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_range_partition::=
partition_attributes::=, add_hash_subpartition::=, update_global_index_clause::=, parallel_clause::=, alter_mapping_table_clauses::=)modify_list_partition::=
modify_table_subpartition::=
add_table_partition::=
list_values_clause::=
range_values_clause::=
partitioning_storage_clause::=
partition_attributes::=
physical_attributes_clause::=, logging_clause::=, allocate_extent_clause::=, deallocate_unused_clause::=, data_segment_compression::=, modify_LOB_parameters::=)
table_partition_description::=
partition_level_subpartition::=
partition_spec::=
subpartition_spec::=
segment_attributes_clause::=, index_org_table_clause::=, LOB_storage_clause::=, varray_col_properties::=)
using_index_clause::=
global_partitioned_index::=
index_partitioning_clause::=
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 the |
Specify the schema containing the table. If you omit schema, then Oracle assumes the table is in your own schema.
Specify the name of the table to be altered.
You can modify, drop columns from, or rename a temporary table. However, for a temporary table you cannot:
LOB_storage_clause for an added or modified LOB column: TABLESPACE, storage_clause, logging_clause, or the LOB_index_clause.physical_attributes_clause, nested_table_col_properties, parallel_clause, allocate_extent_clause, deallocate_unused_clause, or any of the index organized table clauses.logging_clause.MOVE.You can add, drop, or modify the columns of an external table. However, for an external table you cannot:
LONG, LOB, or object type column or change the datatype of an external table column to any of these datatypes.logging_clause.MOVE.
|
Note: If you alter a table that is a master table for one or more materialized views, then Oracle marks the materialized views |
| See Also:
Oracle9i Data Warehousing Guide for more information on materialized views in general |
Use the alter_table_clauses to modify a database table.
The physical_attributes_clause lets you change the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and storage characteristics.
PCTUSED parameter for the index segment of an index-organized table.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.
See Also:
|
The data_segment_compression clause is valid only for heap-organized tables. Use this clause to instruct Oracle whether to compress data segments to reduce disk and memory use. The COMPRESS keyword enables data segment compression. The NOCOMPRESS keyword disables data segment 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 marked |
See Also:
|
Specify whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against a nonpartitioned table, table partition, all partitions of a partitioned table, or all subpartitions of a partition will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file.
When used with the modify_table_default_attrs clause, this clause affects the logging attribute of a partitioned table.
Thelogging_clause also specifies whether ALTER TABLE ... MOVE and ALTER TABLE ... SPLIT operations will be logged or not logged.
See Also:
|
The supplemental_lg_grp_clauses let you add and drop supplemental redo log groups.
ADD LOG GROUP clause to add a redo log group.DROP LOG GROUP clause to drop a redo log group when it is no longer needed.
| See Also:
Oracle Data Guard Concepts and Administration for information on supplemental redo log groups |
Use 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.
You cannot allocate an extent for a temporary table or for a range- or composite-partitioned table.
| See Also:
|
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:
|
Use the CACHE clauses to indicate how Oracle should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE:
CREATE TABLE statement, NOCACHE is the defaultALTER TABLE statement, the existing value is not changed.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 Oracle places LOB data values in the buffer cache for faster access.
You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.
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.
As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value is either not brought into the buffer cache or brought into the buffer cache and placed at the least recently used end of the LRU list. (The latter is the default behavior.)
You cannot specify NOCACHE for index-organized tables.
Specify MONITORING if you want Oracle to collect modification statistics on table. These statistics are estimates of the number of rows affected by DML statements over a particular period of time. They are available for use by the optimizer or for analysis by the user.
| See Also:
Oracle9i Database Performance Tuning Guide and Reference for more information on using this clause |
Specify NOMONITORING if you do not want Oracle to collect modification statistics on table.
You cannot specify MONITORING or NOMONITORING for a temporary table.
The upgrade_table_clause is relevant for object tables and for relational tables with object columns. It lets you instruct Oracle 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.
Within this clause, you cannot specify object_type_col_properties as a clause of column_properties.
Specify INCLUDING DATA if you want Oracle to convert the data in the table to the latest type version format (if it was not converted when the type was altered). 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.
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.
Specify NOT INCLUDING DATA if you want Oracle to leave column data unchanged.
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:
|
The records_per_block_clause lets you specify whether Oracle 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 small (compressed) as possible.
MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index.Specify MINIMIZE to instruct Oracle to calculate the largest number of records in any block in the table, and limit future inserts so that no block can contain more than that number of records.
Oracle Corporation recommends that a representative set of data already exist in the table before you specify MINIMIZE. If you are using data segment compression (see data_segment_compression), then a representative set of compressed data should already exist in the table.
You cannot specify MINIMIZE for an empty table.
Specify NOMINIMIZE to disable the MINIMIZE feature. This is the default.
Use the RENAME clause to rename table to new_table_name.
You cannot rename a materialized view.
|
Note: Using this clause invalidates any dependent materialized views. For more information on materialized views, see CREATE MATERIALIZED VIEW and Oracle9i Data Warehousing Guide. |
The row_movement_clause lets you specify whether Oracle can move a table row. It is possible for a row to move, for example, during data segment compression or an update operation on partitioned data.
ENABLE to allow Oracle to move a row, thus changing the rowid.DISABLE if you want to prevent Oracle from moving a row, thus preventing a change of rowid.You cannot specify this clause for a nonpartitioned index-organized table.
See index_org_table_clause in the context of CREATE TABLE.
The alter_overflow_clause lets you change the definition of 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.
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.
You cannot specify PCTTHRESHOLD for individual partitions of an index-organized table.
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.
You cannot specify this clause for individual partitions of an index-organized table.
overflow_attributes
The overflow_attributes let you specify the overflow data segment physical storage and logging attributes to be modified for the index-organized table. Parameters specified in this clause are applicable only to the overflow data segment.
The 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:
PARTITION, then Oracle automatically allocates an overflow segment for each partition. The physical attributes of these segments are inherited from the table level.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 Oracle uses the tablespace specified for the table. If you do not specify TABLESPACE at the table level, then Oracle uses the tablespace of the partition's primary key index segment.
| See Also:
|
The alter_mapping_table_clauses is valid only if table is index organized and has a mapping table.
Specify UPDATE BLOCK REFERENCES to update all stale "guess" data block addresses stored as part of the logical ROWID column in the mapping table with the correct address for the corresponding block identified by the primary key.
Use the allocate_extent_clause to allocate a new extent at the end of the mapping table for the index-organized table.
| See Also:
|
Specify the deallocate_unused_clause to deallocate unused space at the end of the mapping table of the index-organized table.
| See Also:
|
The keyword is relevant only if table is index organized. Specify COALESCE to instruct Oracle 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.
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" |
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 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.
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 Oracle 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.
TABLESPACE.NOT NULL constraint if table has any rows unless you also specify the DEFAULT clause.Use the DEFAULT clause to specify a default for a new column or a new default for an existing column. Oracle 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 Oracle 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 long enough to hold the default value.
DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.Use inline_constraint to add a constraint to the new column
This clause lets you describe a new column of type REF.
| See Also:
|
The column_properties determine the storage characteristics of an object, nested table, varray, or LOB column.
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.
Use the object_type_col_properties to specify storage characteristics for a new object column or attribute or an element of a collection column or attribute.
For column, specify an object column or attribute.
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.
ELEMENT, you constrain the element type of a collection column or attribute to a subtype of its declared type.IS OF [TYPE] (ONLY type) clause constrains the type of the object column to a subtype of its declared type.NOT SUBSTITUTABLE AT ALL LEVELS indicates that the object column cannot hold instances corresponding to any of its subtypes. Also, substitution is disabled for any embedded object attributes and elements of embedded nested tables and varrays. The default is SUBSTITUTABLE AT ALL LEVELS.[NOT] SUBSTITUTABLE AT ALL LEVELS.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.)
nested_item, specify the name of a column (or a top-level attribute of the table's object type) whose type is a nested table.
If the nested table is a multilevel collection, then the inner nested table may not have a name. In this case, specify COLUMN_VALUE in place of the nested_item name.
storage_table, specify the name of the table where the rows of nested_item reside. The storage table is created in the same schema and the same tablespace as the parent table.parallel_clause.TABLESPACE (as part of the segment_attributes_clause) for a nested table. The tablespace is always that of the parent table.CLUSTER as part of the physical_properties clause.
The varray_col_properties clause lets you specify separate storage characteristics for the LOB in which a varray will be stored. If you specify this clause, then Oracle will always store the varray in a LOB, even if it is small enough to be stored inline. If varray_item is a multilevel collection, then Oracle stores all collection items nested within varray_item in the same LOB in which varray_item is stored.
You cannot specify TABLESPACE as part of LOB_parameters for a varray column. The LOB tablespace for a varray defaults to the containing table's tablespace.
Use the LOB_storage_clause to specify the LOB storage characteristics for a newly added LOB column, partition, or subpartition. You cannot use this clause to modify an existing LOB. Instead, you must use the modify_LOB_storage_clause.
CACHE READS applies only to LOB storage. It indicates that LOB values are brought into the buffer cache only during read operations, but not during write operations.
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.LOB_segname, specify the name of the LOB data segment. You cannot use LOB_segname if more than one LOB_item is specified.When you add a new LOB column, you can specify the logging attribute with CACHE READS, as you can when defining a LOB column at create time.
When you modify a LOB column from CACHE or NOCACHE to CACHE READS, or from CACHE READS to CACHE or NOCACHE, you can change the logging attribute. If you do not specify LOGGING or NOLOGGING, then this attribute defaults to the current logging attribute of the LOB column.
For existing LOBs, if you do not specify CACHE, NOCACHE, or CACHE READS, then Oracle retains the existing values of the LOB attributes.
LOB_parameters you can specify for a hash partition or hash subpartition is TABLESPACE.LOB_index_clause if table is partitioned.Specify whether the LOB value is to be stored in the row (inline) or outside of the row (out of line). (The LOB locator is always stored inline regardless of where the LOB value is stored.)
ENABLE specifies that the LOB value is stored inline if its length is less than approximately 4000 bytes minus system control information. This is the default.DISABLE specifies that the LOB value is stored out of line regardless of the length of the LOB value.You cannot change STORAGE IN ROW once it is set. Therefore, you cannot specify this clause as part of the modify_col_properties clause. However, you can change this setting when adding a new column (add_column_clause) or when moving the table (move_table_clause).
Specify the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, then Oracle rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and integer is 2050, then Oracle allocates 4096 bytes (2 blocks).The maximum value is 32768 (32 K), which is the largest Oracle block size allowed. The default CHUNK size is one Oracle database block.
CHUNK once it is set.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 Oracle returns an error.Specify the maximum percentage of overall LOB storage space to be 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 10% of the overall LOB storage space is used.
If the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION.
You cannot specify RETENTION if the database is running in manual undo mode.
| See Also:
|
If the database is in automatic undo mode, then you can use this clause to specify the number of freelist groups for this LOB. This clause overrides any prior setting of FREELIST GROUPS.
You cannot specify FREEPOOLS if the database is running in manual undo mode.
| See Also:
|
This clause has been deprecated since Oracle8i. Oracle generates an index for each LOB column. The LOB indexes are system named and system managed, and they reside in the same tablespace as the LOB data segments.
It is still possible for you to specify this clause in some cases. However, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data.
| See Also:
Oracle9i Database Migration Guide for information on how Oracle manages LOB indexes in tables migrated from earlier versions |
The LOB_partition_storage clause lets you specify a separate LOB_storage_clause or varray_col_properties clause for each partition. You must specify the partitions in the order of partition position. You can find the order of the partitions by querying the PARTITION_NAME and PARTITION_POSITION columns of the USER_IND_PARTITIONS view.
If you do not specify a LOB_storage_clause or varray_col_properties clause for a particular partition, then the storage characteristics are those specified for the LOB item at the table level. If you also did not specify any storage characteristics for the LOB item at the table level, then Oracle stores the LOB data partition in the same tablespace as the table partition to which it corresponds.
You can specify only one list of LOB_partition_storage clause in a single ALTER TABLE statement, and all LOB_storage_clauses and varray_col_properties clause must precede the list of LOB_partition_storage clauses.
The XMLType_column_properties let you specify storage attributes for an XMLTYPE column.
XMLType columns can be stored either in LOB or object-relational columns.
STORE AS OBJECT RELATIONAL if you want Oracle to store the XMLType data in object-relational columns. Storing data object relationally lets you define indexes on the relational columns and enhances query performance.
If you specify object-relational storage, you must also specify the XMLSchema_spec clause.
STORE AS CLOB if you want Oracle to store the XMLType data in a CLOB column. Storing data in a CLOB column preserves the original content and enhances retrieval time.
If you specify LOB storage, you can specify either LOB parameters or the XMLSchema_spec clause, but not both. Specify the XMLSchema_spec clause if you want to restrict the table or column to particular schema-based XML instances.
This clause lets you specify the URL of a registered XMLSchema (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, you must already have registered the XMLSchema using the DBMS_XMLSCHEMA package.
See Also:
|
Use the modify_column_clauses to modify the properties of an existing column or the substitutability of an existing object type column.
Use this clause to modify the properties of the column. Any of the optional parts of the column definition (datatype, default value, or constraint) that you omit from this clause remain unchanged.
You can change any column's datatype if all rows for the column contain nulls. However, if you change the datatype of a column in a materialized view container table, then the corresponding materialized view is invalidated.
You can omit the datatype only if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.
You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the columns contain nulls. You can reduce the size of a column's datatype as long as the change does not require data to be modified. Oracle scans existing data and returns an error if data exists that exceeds the new length limit.
You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.
If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.
You can change a LONG column to a CLOB or NCLOB column, and a LONG RAW column to a BLOB column.
LONG column. If you wish to change any constraints, then you must do so in a subsequent ALTER TABLE statement.LONG column, then you must drop them before modifying the column to a LOB.See Also:
|
For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.
See Also:
|
The only type of integrity constraint that you can add to an existing column using the MODIFY clause is a NOT NULL constraint, and only if the column contains no nulls. To define other types of integrity constraints (UNIQUE, PRIMARY KEY, referential integrity, and CHECK constraints) on existing columns, use the add_column_clause. To modify existing constraints on existing columns, use the constraint_clauses.
ROWID for an index-organized table, but you can specify a column of type UROWID.REF.
| See Also:
ALTER MATERIALIZED VIEW for information on revalidating a materialized view |
Use this clause to set or change the substitutability of an existing object type column.
The FORCE keyword drops any hidden columns containing typeid information or data for subtype attributes. You must specify FORCE if the column or any attributes of its type are not FINAL.
ALTER TABLE statement.IS OF TYPE syntax (see substitutable_column_clause), which limits the range of subtypes permitted in an object column or attribute to a particular subtype.NOT SUBSTITUTABLE if any of its attributes of nested object types is not FINAL, even by specifying FORCE.The drop_column_clause lets you free space in the database by dropping columns you no longer need, or by marking them to be dropped at a future time when the demand on system resources is less.
BFILE column, then only the locators stored in that column are removed, not the files referenced by the locators.INCLUDING column, then the column stored immediately before this column will become the new INCLUDING column.Specify SET UNUSED to mark one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table (that is, it does not restore the disk space used by these columns). Therefore, the response time is faster than it would be if you execute the DROP clause.
You can view all tables with columns marked UNUSED in the data dictionary views USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, and ALL_UNUSED_COL_TABS.
| See Also:
Oracle9i Database Reference for information on the data dictionary views |
Unused columns are treated as if they were dropped, even though their column data remains in the table's rows. After a column has been marked UNUSED, you have no access to that column. A "SELECT *" query will not retrieve data from unused columns. In addition, the names and types of columns marked UNUSED will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column.
| See Also:
CREATE TABLE for more information on the 1000-column limit |
Specify DROP to remove the column descriptor and the data associated with the target column from each row in the table. If you explicitly drop a particular column, then all columns currently marked UNUSED in the target table are dropped at the same time.
When the column data is dropped:
FORCE option and drops any statistics collected using the statistics type.
| See Also:
DISASSOCIATE STATISTICS for more information on disassociating statistics types |
Specify DROP UNUSED COLUMNS to remove from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table. If the table contains no unused columns, then the statement returns with no errors.
Specify one or more columns to be set as unused or dropped. Use the COLUMN keyword only if you are specifying only one column. If you specify a column list, then it cannot contain duplicates.
Specify CASCADE CONSTRAINTS if you want to drop all foreign key constraints that refer to the primary and unique keys defined on the dropped columns, and drop all multicolumn constraints defined on the dropped columns. If any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specify CASCADE CONSTRAINTS. Otherwise, the statement aborts and an error is returned.
The INVALIDATE keyword is optional. Oracle automatically invalidates all dependent objects, such as views, triggers, and stored program units. Object invalidation is a recursive process. Therefore, all directly dependent and indirectly dependent objects are invalidated. However, only local dependencies are invalidated, because Oracle manages remote dependencies differently from local dependencies.
An object invalidated by this statement is automatically revalidated when next referenced. You must then correct any errors that exist in that object before referencing it.
| See Also:
Oracle9i Database Concepts for more information on dependencies |
Specify CHECKPOINT if you want Oracle to apply a checkpoint for the DROP COLUMN operation after processing integer rows; integer is optional and must be greater than zero. If integer is greater than the number of rows in the table, then Oracle applies a checkpoint after all the rows have been processed. If you do not specify integer, then Oracle sets the default of 512. Checkpointing cuts down the amount of undo logs accumulated during the DROP COLUMN operation to avoid running out of rollback segment space. However, if this statement is interrupted after a checkpoint has been applied, then the table remains in an unusable state. While the table is unusable, the only operations allowed on it are DROP TABLE, TRUNCATE TABLE, and ALTER TABLE DROP COLUMNS CONTINUE (described in sections that follow).
You cannot use this clause with SET UNUSED, because that clause does not remove column data.
Specify DROP COLUMNS CONTINUE to continue the drop column operation from the point at which it was interrupted. Submitting this statement while the table is in a valid state results in an error.
ALTER TABLE clauses. For example, the following statements are not allowed:
ALTER TABLE t1 DROP COLUMN f1 DROP (f2); ALTER TABLE t1 DROP COLUMN f1 SET UNUSED (f2); ALTER TABLE t1 DROP (f1) ADD (f2 NUMBER); ALTER TABLE t1 SET UNUSED (f3) ADD (CONSTRAINT ck1 CHECK (f2 > 0));
ALTER TYPE ... DROP ATTRIBUTE statement with the CASCADE INCLUDING TABLE DATA clause. Be aware that dropping an attribute affects all dependent objects. See DROP ATTRIBUTE for more information.CASCADE CONSTRAINTS.SCOPE table constraint or a WITH ROWID constraint on a REF column.Use the rename_column_clause to rename a column of table. The new column name must not be the same as any other column name in table.
When you rename a column, Oracle handles dependent objects as follows:
INVALID. Oracle attempts to revalidate them when they are next accessed, but you may need to alter these objects with the new column name if revalidation fails.column_clauses in the same statement.Use the modify_collection_retrieval clause to change what Oracle returns when a collection item is retrieved from the database.
Specify the name of a column-qualified attribute whose type is nested table or varray.
Specify what Oracle should return as the result of a query:
LOCATOR specifies that a unique locator for the nested table is returned.VALUE specifies that a copy of the nested table itself is returned.
The modify_LOB_storage_clause lets you change the physical attributes of LOB_item. You can specify only one LOB_item for each modify_LOB_storage_clause.
The REBUILD FREEPOOLS clause removes all the old data from the LOB column. This clause is useful only if you reverting to PCTVERSION for management of LOBs. You might want to do this to manage older data blocks, and you must do this if you are downgrading to a release of Oracle earlier than 9.2.0.
INITIAL parameter in the storage_clause when modifying the LOB storage attributes.allocate_extent_clause and the deallocate_unused_clause in the same statement.| See Also:
|
The alter_varray_col_properties clause lets you change the storage characteristics of an existing LOB in which a varray is stored.
You cannot specify the TABLESPACE clause of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the tablespace of the containing table.
Use the constraint_clauses to add a new constraint using out-of-line declaration, modify the state of an existing constraint, or to drop a constraint.
| See Also:
|
The ADD clause lets you add a new out-of-line constraint or out-of-line REF constraint to the table.
| See Also:
"Disabling a CHECK Constraint: Example", "Specifying Object Identifiers: Example", and "REF Columns: Examples" |
The MODIFY CONSTRAINT clause lets you change the state of an existing constraint.
NOT DEFERRABLE constraint to INITIALLY DEFERRED.CHAR column to VARCHAR2 (or VARCHAR) and a VARCHAR2 (or VARCHAR) to CHAR only if the column contains nulls in all rows or if you do not attempt to change the column size.LONG or LONG RAW column to a LOB if it is part of a cluster. If you do change a LONG or LONG RAW column to a LOB, then the only other clauses you can specify in this ALTER TABLE statement are the DEFAULT clause and the LOB_storage_clause.LOB_storage_clause as part of modify_col_properties only when you are changing a LONG or LONG RAW column to a LOB.The RENAME CONSTRAINT clause lets you rename any existing constraint on table. The new constraint name cannot be the same as any existing constraint on any object in the same schema. All objects that are dependent on the constraint remain valid.
The drop_constraint_clause lets you drop an integrity constraint from the database. Oracle stops enforcing the constraint and removes it from the data dictionary. You can specify only one constraint for each drop_constraint_clause, but you can specify multiple drop_constraint_clauses in one statement.
Specify PRIMARY KEY to drop the table's primary key constraint.
Specify UNIQUE to drop the unique constraint on the specified columns.
|
Note: If you drop the primary key or unique constraint from a column on which a bitmap join index is defined, then Oracle invalidates the index. See CREATE INDEX for information on bitmap join indexes. |
Specify CONSTRAINT constraint to drop an integrity constraint other than a primary key or unique constraint.
Specify CASCADE if you want all other integrity constraints that depend on the dropped integrity constraint to be dropped as well.
Specify KEEP or DROP INDEX to indicate whether Oracle should preserve or drop the index it has been using to enforce the PRIMARY KEY or UNIQUE constraint.
CASCADE clause. If you omit CASCADE, then Oracle does not drop the primary key or unique constraint if any foreign key references it.CASCADE clause) on a table that uses the primary key as its object identifier (OID).REF column, then the REF column remains scoped to the referenced table.Use the alter_external_table_clause to change the characteristics of an external table. This clause has no affect on the external data itself. The syntax and semantics of the parallel_clause, enable_disable_clause, external_data_properties, and REJECT LIMIT clause are the same as described for CREATE TABLE. See the external_table_clause of CREATE TABLE.
LONG, LOB, or object type column to an external table, nor can you change the datatype of an external table column to any of these datatypes.The clauses in this section apply only to partitioned tables. You cannot combine partition operations with other partition operations or with operations on the base table in the same ALTER TABLE statement.
CONTEXT domain index, please refer to Oracle Text Reference.table, then any operation that alters a partition of table causes Oracle to mark the index UNUSABLE.
|
Note: The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle9i Database Administrator's Guide for a discussion of these restrictions. |
The modify_table_default_attrs clause lets you specify new default values for the attributes of table. Partitions and LOB partitions you create subsequently will inherit these values unless you override them explicitly when creating the partition or LOB partition. Existing partitions and LOB partitions are not affected by this clause.
Only attributes named in the statement are affected, and the default values specified are overridden by any attributes specified at the individual partition level.
FOR PARTITION applies only to composite-partitioned tables. This clause specifies new default values for the attributes of partition. Subpartitions and LOB subpartitions of partition that you create subsequently will inherit these values unless you override them explicitly when creating the subpartition or LOB subpartition. Existing subpartitions are not affected by this clause.PCTTHRESHOLD, key_compression, and the alter_overflow_clause are valid only for partitioned index-organized tables. However, in the key_compression clause, you cannot specify an integer after the COMPRESS keyword. Key compression length can be specified only when you create the table.PCTUSED parameter in segment_attributes for the index segment of an index-organized table.key_compression_clause only if key compression is already specified at the table level.Use the set_subpartition_template clause to create or replace existing default list or hash subpartition definitions for each table partition. This clause is valid only for composite-partitioned tables. It replaces the existing subpartition template or creates a new template if you have not previously created one. Existing subpartitions are not affected, nor are existing local and global indexes. However, subsequent partitioning operations (such as add and merge operations) will use the new template.
You can drop an existing subpartition template by specifying ALTER TABLE table SET SUBPARTITION TEMPLATE ().
list_values_clause.hash_subpartition_quantity clause.partitioning_storage_clause you can specify for subpartitions is the TABLESPACE clause.The modify_table_partition clause lets you change the real physical attributes of a range, hash, or list partition. This clause optionally modifies the storage attributes of one or more LOB items for the partition. You can specify new values for physical attributes (with some restrictions, as noted in the sections that follow), logging; and storage parameters.
You can also specify how Oracle should handle local indexes that become unusable as a result of the modification to the partition. See "UNUSABLE LOCAL INDEXES Clauses".
For partitioned index-organized tables, you can also update the mapping table in conjunction with partition changes. See the alter_mapping_table_clauses.
When modifying a range partition, if table is composite partitioned:
allocate_extent_clause, then Oracle allocates an extent for each subpartition of partition.deallocate_unused_clause, then Oracle deallocates unused storage from each subpartition of partition.partition as well, overriding existing values. To avoid changing the attributes of existing subpartitions, use the FOR PARTITION clause of modify_table_default_attrs.If you specify UNUSABLE LOCAL INDEXES, then you cannot specify any other clause of modify_range_partition.
This clause is valid only for range-hash composite partitions. The add_hash_subpartition clause lets you add a hash subpartition to partition. Oracle populates the new subpartition with rows rehashed from the other subpartition(s) of partition as determined by the hash function. For optimal load balancing, the total number of subpartitions should be a power of 2.
subpartition, then Oracle assigns a name in the form SYS_SUBPn.list_values_clause is not valid for this operation.partitioning_storage_clause, the only clause you can specify for subpartitions is the TABLESPACE clause. If you do not specify TABLESPACE, then the new subpartition will reside in the default tablespace of partition.Oracle invalidates any global indexes on table. You can update these indexes during this operation using the update_global_index_clause.
Oracle adds local index partitions corresponding to the selected partition. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to the added partitions.
the add_list_subpartition clause lets you add a list subpartition to partition. This clause is valid only for range-list composite partitions, and only if you have not already created a DEFAULT subpartition.
subpartition, then Oracle assigns a name in the form SYS_SUBPn.list_values_clause is required in this operation, and the values you specify in the list_values_clause cannot exist in any other subpartition of partition. However, these values can duplicate values found in subpartitions of other partitions.partitioning_storage_clause, the only clause you can specify for subpartitions is the TABLESPACE clause. If you do not specify TABLESPACE, then Oracle stores the new subpartition in the default tablespace of partition. If partition has no default tablespace, then Oracle uses the default tablespace of table. If table has no default tablespace, then Oracle uses the default tablespace of the user.Oracle also adds a subpartition with the same value list to all local index partitions of the table. The status of existing local and global index partitions of table are not affected.
You cannot specify this clause if you have already created a DEFAULT subpartition for this partition. Instead you must split the DEFAULT partition using the split_list_subpartition clause.
When modifying a hash partition, in the partition_attributes clause, you can specify only the allocate_extent_clause and deallocate_unused_clause. All other attributes of the partition are inherited from the table-level defaults except TABLESPACE, which stays the same as it was at create time.
COALESCE SUBPARTITION applies only to hash subpartitions. Use the COALESCE SUBPARTITION clause if you want Oracle to select the last hash subpartition, distribute its contents into one or more remaining subpartitions (determined by the hash function), and then drop the last subpartition.
Oracle invalidates any global indexes on table. You can update these indexes during this operation using the update_global_index_clause.
Oracle drops local index partitions corresponding to the selected partition. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to one or more absorbing partitions.
If you specify UNUSABLE LOCAL INDEXES, then you cannot specify any other clause of modify_hash_partition.
When modifying a list partition, the following additional clauses are available:
If you specify UNUSABLE LOCAL INDEXES, then you cannot specify any other clause of modify_list_partition.
These clauses are valid only when you are modifying list partitions. Local and global indexes on the table are not affected by either of these clauses.
ADD VALUES clause to extend the partition_value list of partition to include additional values. The added partition values must comply with all rules and restrictions listed in the list_partitioning of CREATE TABLE.DROP VALUES clause to reduce the partition_value list of partition by eliminating one or more partition_value. When you specify this clause, Oracle checks to ensure that no rows with this value exist. If such rows do exist, then Oracle returns an error.
table contains a default partition and you attempt to add values to a nondefault partition, then Oracle will check that the values being added do not already exist in the default partition. If the values do exist in the default partition, then Oracle returns an error.This clause applies only to composite-partitioned tables.
The modify_hash_subpartition clause lets you allocate or deallocate storage for an individual subpartition of table. This clause is valid only for range-hash composite-partitioned tables.
You can also specify how Oracle should handle local indexes that become unusable as a result of the modification to the partition. See "UNUSABLE LOCAL INDEXES Clauses".
The only modify_LOB_parameters you can specify for subpartition are the allocate_extent_clause and deallocate_unused_clause.
The modify_list_subpartition clause lets you make the same changes to a list subpartition that you can make to a hash subpartition. In addition, it lets you add or remove values from a list subpartition's value list. This clause is valid only for range-list composite-partitioned tables.
Specify ADD VALUES to extend the value list of subpartition.
subpartition or of any other subpartition of the same partition. However, the values can exist in the value lists of subpartitions of other partitions.DEFAULT subpartition, then Oracle verifies that none of the values you are adding exist in rows of the DEFAULT subpartition. If the added values do exist in the DEFAULT subpartition, then the statement will fail.Specify DROP VALUES to remove one or more values from the value list of subpartition.
subpartition.ALTER TABLE ... DROP SUBPARTITION statement.subpartition contains any rows containing one of the values being dropped, then the operation fails and Oracle returns an error. You must first delete any rows containing the values you wish to drop before reissuing the statement.You can also specify how Oracle should handle local indexes that become unusable as a result of the modification to the partition. See "UNUSABLE LOCAL INDEXES Clauses".
The only modify_LOB_parameters you can specify for subpartition are the allocate_extent_clause and deallocate_unused_clause.
Use the move_table_partition clause to move partition to another segment. You can move partition data to another tablespace, recluster data to reduce fragmentation, or change create-time physical attributes.
If the table contains LOB columns, then you can use the LOB_storage_clause to move the LOB data and LOB index segments associated with this partition. Only the LOBs named are affected. If you do not specify the LOB_storage_clause for a particular LOB column, then its LOB data and LOB index segments are not moved.
Oracle invalidates any global indexes on heap-organized tables. You can update these indexes during this operation using the update_global_index_clause. Global indexes on index-organized tables are primary key based, so they do not become unusable.
Oracle moves local index partitions corresponding to the specified partition. If the moved partitions are not empty, then Oracle marks them UNUSABLE, and you must rebuild them.
When you move a LOB data segment, Oracle drops the old data segment and corresponding index segment and creates new segments even if you do not specify a new tablespace.
The move operation obtains its parallel attribute from the parallel_clause, if specified. If not specified, the default parallel attributes of the table, if any, are used. If neither is specified, then Oracle performs the move without using parallelism.
Specifying the parallel_clause in MOVE PARTITION does not change the default parallel attributes of table.
| See Also:
Oracle9i Database Concepts for more information on logical rowids and "Moving Table Partitions: Example" |
The MAPPING TABLE clause is relevant only for an index-organized table that already has a mapping table defined for it. Oracle moves the mapping table along with the index partition and marks all corresponding bitmap index partitions UNUSABLE.
| See Also:
|
partition is a hash partition, then the only attribute you can specify in this clause is TABLESPACE.move_table_subpartition_clause.Use the move_table_subpartition clause to move subpartition to another segment. If you do not specify TABLESPACE, then the subpartition remains in the same tablespace.
You can update global indexes on table during this operation using the update_global_index_clause. If the subpartition is not empty, then Oracle marks UNUSABLE, and you must rebuild, all local index subpartitions corresponding to the subpartition being moved.
If the table contains LOB columns, then you can use the LOB_storage_clause to move the LOB data and LOB index segments associated with this subpartition. Only the LOBs specified are affected. If you do not specify the LOB_storage_clause for a particular LOB column, then its LOB data and LOB index segments are not moved.
When you move a LOB data segment, Oracle drops the old data segment and corresponding index segment and creates new segments even if you do not specify a new tablespace.
In subpartition_spec, the only clause of the partitioning_storage_clause you can specify is the TABLESPACE clause.
Use the add_table_partition clause to add a hash, range, or list partition to table.
Oracle adds to any local index defined on table a new partition with the same name as that of the base table partition. If the index already has a partition with such a name, then Oracle generates a partition name of the form SYS_Pn.
If table is index organized, then Oracle adds a partition to any mapping table and overflow area defined on the table as well.
The add_range_partition_clause lets you add a new range partition to the "high" end of a partitioned table (after the last existing partition). You can specify any create-time physical attributes for the new partition. If the table contains LOB columns, then you can also specify partition-level attributes for one or more LOB items.
If you do not specify a new partition_name, then Oracle assigns a name of the form SYS_Pn. If you add a range partition to a composite-partitioned table and do not describe the subpartitions, then Oracle assigns subpartition names as described in partition_level_subpartition.
If a domain index is defined on table, then the index must not be marked IN_PROGRESS or FAILED.
A table can have up to 64K-1 partitions.
MAXVALUE, then you cannot add a partition to the table. Instead, use the split_table_partition clause to add a partition at the beginning or the middle of the table.key_compression and OVERFLOW clauses are valid only for a partitioned index-organized table. You can specify OVERFLOW only if the partitioned table already has an overflow segment. You can specify key compression only if key compression is enabled at the table level.PCTUSED parameter for the index segment of an index-organized table.Specify the upper bound for the new partition. The value_list is a comma-delimited, ordered list of literal values corresponding to column_list. The value_list must collate greater than the partition bound for the highest existing partition in the table.
The partition_level_subpartition clause (in table_partition_description) is valid only for a composite-partitioned table. This clause lets you specify hash or list subpartitions for a new range-hash or range-list composite partition. This clause overrides any subpartition descriptions defined in subpartition_template at the table level.
For all composite partitions:
SYS_SUBPn. The number of tablespaces does not have to equal the number of subpartitions. If the number of partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.subpartition_spec to specify individual subpartitions by name, and optionally the tablespace where each should be stored.partition_level_subpartition and if you have created a subpartition template, Oracle uses the template to create subpartitions. If you have not created a subpartition template, Oracle creates one hash subpartition or one DEFAULT list subpartition.partition_level_subpartition entirely, Oracle assigns subpartition names as follows:
partition_name underscore (_) subpartition_name" (for example, P1_SUB1).SYS_SUBPn.partition_spec, the only clause of the partitioning_storage_clause you can specify is the TABLESPACE clause.For range-hash composite partitions, the list_values_clause of subpartition_spec is not relevant and is invalid.
For range-list composite partitions:
hash_subpartition_quantity is not relevant, so you must use the lower branch of partition_level_subpartition.subpartition_spec, you must specify the list_values_clause for each subpartition, and the values you specify for each subpartition cannot exist in any other subpartition of the same partition.
Oracle will add a new index partition with the same subpartition descriptions to all local indexes defined on table. Global indexes defined on table are not affected.
The add_hash_partition_clause lets you add a new hash partition to the "high" end of a partitioned table. Oracle will populate the new partition with rows rehashed from other partitions of table as determined by the hash function. For optimal load balancing, the total number of partitions should be a power of 2.
You can specify a name for the partition, and optionally a tablespace where it should be stored. If you do not specify a name, then Oracle assigns a partition name of the form SYS_Pn. If you do not specify TABLESPACE, then the new partition is stored in the table's default tablespace. Other attributes are always inherited from table-level defaults.
You can update global indexes on table during this operation using the update_global_index_clause. For a heap-organized table, if this operation causes data to be rehashed among partitions, then Oracle marks UNUSABLE, and you must rebuild, any corresponding local index partitions. Indexes on index-organized tables are primary key based, so they do not become unusable.
Use the parallel_clause to specify whether to parallelize the creation of the new partition.
| See Also:
CREATE TABLE and Oracle9i Database Concepts for more information on hash partitioning |
In table_partition_description, you cannot specify partition_level_subpartition.
The add_list_partition_clause lets you add a new partition to table using a new set of partition values. You can specify any create-time physical attributes for the new partition. If the table contains LOB columns, then you can also specify partition-level attributes for one or more LOB items.
When you add a list partition to a table, Oracle adds a corresponding index partition with the same value list to all local indexes defined on the table. Global indexes are not affected.
table_partition_description, you cannot specify partition_level_subpartition.DEFAULT partition for the table. Instead you must use the split_table_partition clause to split the DEFAULT partition.
See Also:
|
COALESCE applies only to hash partitions. Use the coalesce_table_partition clause to indicate that Oracle should select the last hash partition, distribute its contents into one or more remaining partitions (determined by the hash function), and then drop the last partition.
Oracle invalidates any global indexes on heap-organized tables. You can update these indexes during this operation using the update_global_index_clause. Global indexes on index-organized tables are primary key based, so they do not become unusable.
Oracle drops local index partitions corresponding to the selected partition. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to one or more absorbing partitions.
The drop_table_partition clause removes partition, and the data in that partition, from a partitioned table. If you want to drop a partition but keep its data in the table, then you must merge the partition into one of the adjacent partitions.
If the table has LOB columns, then Oracle also drops the LOB data and LOB index partitions (and their subpartitions, if any) corresponding to partition.
If table is index organized and has a mapping table defined on it, then Oracle drops the corresponding mapping table partition as well.
Oracle drops local index partitions and subpartitions corresponding to partition, even if they are marked UNUSABLE.
You can update global indexes on heap-organized tables during this operation using the update_global_index_clause. If you specify the parallel_clause with the update_global_index_clause, then Oracle parallelizes the index update, not the drop operation.
If you drop a range partition and later insert a row that would have belonged to the dropped partition, then Oracle stores the row in the next higher partition. However, if that partition is the highest partition, then the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.
table contains only one partition, then you cannot drop the partition. You must drop the table.
Use this clause to drop a list subpartition from a range-list composite-partitioned table. Oracle deletes any rows in the dropped subpartition.
Oracle drops the corresponding subpartition of any local index. Other index subpartitions are not affected. Any global indexes are marked UNUSABLE unless you specify the update_global_index_clause.
MODIFY PARTITION ... COALESCE SUBPARTITION syntax.drop_table_partition clause.Use the rename_table_partition clause to rename a table partition or subpartition current_name to new_name. For both partitions and subpartitions, new_name must be different from all existing partitions and subpartitions of the same table.
If table is index organized, then Oracle assigns the same name to the corresponding primary key index partition as well as to any existing overflow partitions and mapping table partitions.
Specify TRUNCATE PARTITION to remove all rows from partition or, if the table is composite partitioned, all rows from partition's subpartitions. Specify TRUNCATE SUBPARTITION to remove all rows from subpartition. If table is index organized, then Oracle also truncates any corresponding mapping table partitions and overflow area partitions.
If the partition or subpartition to be truncated contains data, then you must first disable any referential integrity constraints on the table. Alternatively, you can delete the rows and then truncate the partition.
If the table contains any LOB columns, then the LOB data and LOB index segments for this partition are also truncated. If table is composite partitioned, then the LOB data and LOB index segments for this partition's subpartitions are truncated.
If a domain index is defined on table, then the index must not be marked IN_PROGRESS or FAILED, and the index partition corresponding to the table partition being truncated must not be marked IN_PROGRESS.
For each partition or subpartition truncated, Oracle also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked UNUSABLE, then Oracle truncates them and resets the UNUSABLE marker to VALID.
You can update global indexes on table during this operation using the update_global_index_clause. If you specify the parallel_clause with the update_global_index_clause, then Oracle parallelizes the index update, not the truncate operation.
Specify DROP STORAGE to deallocate space from the deleted rows and make it available for use by other schema objects in the tablespace.
Specify REUSE STORAGE to keep space from the deleted rows allocated to the partition or subpartition. The space is subsequently available only for inserts and updates to the same partition or subpartition.
The split_table_partition clause lets you create, from current_partition, two new partitions, each with a new segment and new physical attributes, and new initial extents. The segment associated with current_partition is discarded.
The new partitions inherit all unspecified physical attributes from current_partition.
|
Note: Oracle can optimize and speed up |
If you split a DEFAULT list partition, then the first of the resulting partitions will have the split values, and the second resulting partition will have the DEFAULT value.
If table is index organized, then Oracle splits any corresponding mapping table partition and places it in the same tablespace as the parent index-organized table partition. Oracle also splits any corresponding overflow area, and you can specify segment attributes for the new overflow areas using the OVERFLOW clause.
Oracle splits the corresponding local index partition, even if it is marked UNUSABLE. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to the split partitions. The new index partitions inherit their attributes from the partition being split. Oracle stores the new index partitions in the default tablespace of the index partition being split. If that index partition has no default tablespace, then Oracle uses the tablespace of the new underlying table partitions.
If table contains LOB columns, then you can use the LOB_storage_clause to specify separate LOB storage attributes for the LOB data segments resulting from the split. Oracle drops the LOB data and LOB index segments of current_partition and creates new segments for each LOB column, for each partition, even if you do not specify a new tablespace.
The AT clause applies only to range partitions. Specify the new noninclusive upper bound for the first of the two new partitions. The value list must compare less than the original partition bound for current_partition and greater than the partition bound for the next lowest partition (if there is one).
The VALUES clause applies only to list partitions. Specify the partition values you want to include in the first of the two new partitions. Oracle creates the first new partition using the partition value list you specify and creates the second new partition using the remaining partition values from current_partition. Therefore, the value list cannot contain all of the partition values of current_partition, nor can it contain any partition values that do not already exist for current_partition.
The INTO clause lets you describe the two partitions resulting from the split. In function_spec, the keyword PARTITION is required even if you do not specify the optional names and physical attributes of the two partitions resulting from the split. If you do not specify new partition names, then Oracle assigns names of the form SYS_Pn. Any attributes you do not specify are inherited from current_partition.
For range-hash composite-partitioned tables, if you specify subpartitioning for the new partitions, then you can specify only TABLESPACE for the subpartitions. All other attributes are inherited from current_partition. If you do not specify subpartitioning for the new partitions, then their tablespace is also inherited from current_partition.
For range-list composite-partitioned tables, you cannot specify subpartitions for the new partitions at all (using the partition_level_subpartition clause of table_partition_description). The subpartitions of the split partition will inherit all their attributes (number of subpartitions and value lists) from current_partition.
For all range-list composite-partitioned tables, and for range-hash composite-partitioned tables for which you do not specify subpartition names for the newly created subpartitions, the newly created subpartitions inherit their names from the parent partition as follows:
partition_name underscore (_) subpartition_name" (for example, P1_SUBP1), Oracle generates corresponding names in the newly created subpartitions using the new partition names (for example P1A_SUB1 and P1B_SUB1).SYS_SUBPn.Oracle splits the corresponding partition in each local index defined on table, even if the index is marked UNUSABLE.
Oracle invalidates any global indexes on heap-organized tables. You can update these indexes during this operation using the update_global_index_clause. Global indexes on index-organized tables are primary key based, so they do not become unusable.
The parallel_clause lets you parallelize the split operation, but does not change the default parallel attributes of the table.
partition_spec, you can specify the key_compression clause and OVERFLOW clause only for a partitioned index-organized table. Also, you cannot specify the PCTUSED parameter for the index segment of an index-organized table.
Use this clause to split a list subpartition into two separate subpartitions with nonoverlapping value lists.
|
Note: Oracle can optimize and speed up |
VALUES clause, specify the subpartition values you want to include in the first of the two new subpartitions. You can specify NULL if you have not already specified NULL for another subpartition in the same partition. Oracle creates the first new subpartition using the subpartition value list you specify and creates the second new partition using the remaining partition values from the current subpartition. Therefore, the value list cannot contain all of the partition values of the current subpartition, nor can it contain any partition values that do not already exist for the current subpartition.INTO clause lets you describe the two subpartitions resulting from the split. In subpartition_spec, the keyword PARTITION is required even if you do not specify the optional names and attributes of the two new subpartitions. If you do not specify new subpartition names, or if you omit this clause entirely, then Oracle assigns names of the form SYS_SUBPn. Any attributes you do not specify are inherited from the current subpartition.Oracle splits any corresponding local subpartition index, even if it is marked UNUSABLE. The new index subpartitions will inherit the names of the new table subpartitions unless those names are already held by index subpartitions. In that case, Oracle assigns new index subpartition names of the form SYS_SUBPn. The new index subpartitions inherit physical attributes from the parent subpartition. However, if the parent subpartition does not have a default TABLESPACE attribute, then the new subpartitions inherit the tablespace of the corresponding new table subpartitions.
Oracle marks all global indexes on table UNUSABLE. If you also specify the update_global_index_clause, then Oracle will attempt to rebuild these global indexes.
subpartition_spec, the only clause of partitioning_storage_clause you can specify is the TABLESPACE clause.The merge_table_partitions clause lets you merge the contents of two partitions of table into one new partition, and then drops the original two partitions.
DEFAULT list partition with another list partition, then the resulting partition will be the DEFAULT partition and will have the DEFAULT value.partition_level_subpartition. Oracle obtains the subpartitioning information from any subpartition template. If you have not specified a subpartition template, then Oracle creates exactly one DEFAULT subpartition.Any attributes not specified in the segment_attributes_clause are inherited from table-level defaults.
If you do not specify a new partition_name, then Oracle assigns a name of the form SYS_Pn. If the new partition has subpartitions, then Oracle assigns subpartition names as described in partition_level_subpartition.
Oracle marks UNUSABLE any global indexes on heap-organized tables. You can update these indexes during this operation using the update_global_index_clause. Global indexes on index-organized tables are primary key based, so they do not become unusable.
Oracle drops local index partitions corresponding to the selected partitions. Oracle marks UNUSABLE, and you must rebuild, the local index partition corresponding to merged partition.
You cannot specify this clause for a hash-partitioned table. Instead, use the coalesce_table_partition clause.
| See Also:
"Merging Two Table Partitions: Example" and "Working with Default List Partitions: Example" |
The partition_level_subpartition clause is valid only when you are merging range-hash composite partitions. This clause lets you specify subpartitioning attributes for the newly merged partition. Any attributes not specified in this clause are inherited from table-level values. If you do not specify this clause, then the new merged partition inherits subpartitioning attributes from table-level defaults.
If you omit this clause, then the new partition inherits the subpartition descriptions from any subpartition template you have defined. If you have not defined a subpartition template, then Oracle creates one subpartition in the newly merged partition.
Specify the parallel_clause to parallelize the merge operation.
You cannot specify this clause for a range-list composite partition.
The merge_table_subpartitions clause lets you merge the contents of two list subpartitions of table into one new subpartition, and then drops the original two subpartitions. The two subpartitions to be merged must belong to the same partition, but they do not have to be adjacent. The data in the resulting subpartition will consist of the combined data from the merged subpartitions.
INTO clause entirely, then Oracle assigns a name of the form SYS_SUBPn.INTO clause, then the keyword SUBPARTITION in subpartition_spec is required, you cannot specify the list_values_clause, and the only clause you can specify in the partitioning_storage_clause is the TABLESPACE clause.Any attributes you do not specify explicitly for the new subpartition are inherited from partition-level values. If you reuse one of the subpartition names for the new subpartition, then the new subpartition will inherit values from the subpartition whose name is being reused rather than from partition-level default values.
Oracle merges corresponding local index subpartitions and marks the resulting index subpartition UNUSABLE. Oracle also marks UNUSABLE both partitioned and nonpartitioned global indexes on table.
You cannot specify this clause for a hash subpartition.
Use the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause to exchange the data and index segments of:
In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be identical. In the case of list partitions and subpartitions, the corresponding value lists must also match.
This clause facilitates high-speed data loading when used with transportable tablespaces.
| See Also:
Oracle9i Database Administrator's Guide for information on transportable tablespaces |
If table contains LOB columns, then for each LOB column Oracle exchanges LOB data and LOB index partition or subpartition segments with corresponding LOB data and LOB index segments of table.
All of the segment attributes of the two objects (including tablespace and logging) are also exchanged.
All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. The aggregate statistics of the table receiving the new partition are recalculated.
Oracle invalidates any global indexes on the objects being exchanged. If you specify the update_global_index_clause with this clause, then Oracle updates the global indexes on the table whose partition is being exchanged. Global indexes on the table being exchanged remain invalidated. If you specify the parallel_clause with the update_global_index_clause, then Oracle parallelizes the index update, not the exchange operation.
Specify the table with which the partition or subpartition will be exchanged.
Specify INCLUDING INDEXES if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table).
Specify EXCLUDING INDEXES if you want all index partitions or subpartitions corresponding to the partition and all the regular indexes and index partitions on the exchanged table to be marked UNUSABLE.
Specify WITH VALIDATION if you want Oracle to return an error if any rows in the exchanged table do not map into partitions or subpartitions being exchanged.
Specify WITHOUT VALIDATION if you do not want Oracle to check the proper mapping of rows in the exchanged table.
Specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS. The exceptions table must be on your local database.
You can create the EXCEPTIONS table using one of these scripts:
UTLEXCPT.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)UTLEXPT1.SQL uses universal rowids, so it can accommodate rows from both heap-organized and index-organized tables.If you create your own exceptions table, then it must follow the format prescribed by one of these two scripts.
See Also:
|
UNIQUE constraint, and that constraint must be in DISABLE VALIDATE state.If these conditions are not true, then Oracle ignores this clause.
| See Also:
The |
These two clauses modify the attributes of local index partitions and index subpartitions corresponding to partition (depending on whether you are modifying a partition or subpartition).
UNUSABLE LOCAL INDEXES marks UNUSABLE the local index partition or subpartition associated with partition.REBUILD UNUSABLE LOCAL INDEXES rebuilds the unusable local index partition or subpartition associated with partition.modify_table_partition clause.modify_table_partition clause for a partition that has subpartitions. However, you can specify this clause in the modify_hash_subpartition or modify_list_subpartition clause.When you perform DDL on a table partition, if a global index is defined on table, then Oracle invalidates the entire index, not just the partitions undergoing DDL. This clause lets you update the global index partition you are changing during the DDL operation, eliminating the need to rebuild the index after the DDL.
Specify UPDATE GLOBAL INDEXES to update the global indexes defined on table.
Specify INVALIDATE GLOBAL INDEXES to invalidate the global indexes defined on table.
If you specify neither, then Oracle invalidates the global indexes.
This clause supports only global indexes. Domain indexes and index-organized tables are not supported. In addition, this clause updates only indexes that are USABLE and VALID. UNUSABLE indexes are left unusable, and INVALID global indexes are ignored.
The parallel_clause lets you change the default degree of parallelism for queries and DML on the table.
Specify NOPARALLEL for serial execution. This is the default.
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.
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.
table contains any columns of LOB or user-defined object type, then subsequent INSERT, UPDATE, and DELETE operations on table are executed serially without notification. Subsequent queries, however, are executed in parallel.parallel_clause in conjunction with the move_table_clause, then the parallelism applies only to the move, not to subsequent DML and query operations on the table.
| See Also:
"Notes on the parallel_clause" for |
The move_table_clause lets you relocate data of a nonpartitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.
You can also move any LOB data segments associated with the table using the LOB_storage_clause and varray_col_properties clause. LOB items not specified in this clause are not moved.
For an index-organized table, the index_org_table_clause of the syntax lets you additionally specify overflow segment attributes. The move_table_clause rebuilds the index-organized table's primary key index. The overflow data segment is not rebuilt unless the OVERFLOW keyword is explicitly stated, with two exceptions:
PCTTHRESHOLD or the INCLUDING column as part of this ALTER TABLE statement, then the overflow data segment is rebuilt.The index and data segments of LOB columns are not rebuilt unless you specify the LOB columns explicitly as part of this ALTER TABLE statement.
Specify ONLINE if you want DML operations on the index-organized table to be allowed during rebuilding of the table's primary key index.
MOVE. If you specify ONLINE and then issue parallel DML statements, then Oracle returns an error.Specify MAPPING TABLE if you want Oracle to create a mapping table if one does not already exist. If it does exist, then Oracle moves the mapping table along with the index-organized table, and marks any bitmapped indexes UNUSABLE. The new mapping table is created in the same tablespace as the parent table.
Specify NOMAPPING to instruct Oracle to drop an existing mapping table.
You cannot specify NOMAPPING if any bitmapped indexes have been defined on table.
| See Also:
|
Use the key_compression clause to enable or disable key compression in an index-organized table.
COMPRESS enables key compression, which eliminates repeated occurrence of primary key column values in index-organized tables. Use integer to specify the prefix length (number of prefix columns to compress).
The valid range of prefix length values is from 1 to the number of primary key columns minus 1. The default prefix length is the number of primary key columns minus 1.
NOCOMPRESS disables key compression in index-organized tables. This is the default.Specify the tablespace into which the rebuilt index-organized table is stored.
MOVE, then it must be the first clause, and the only clauses outside this clause that are allowed are the physical_attributes_clause, the parallel_clause, and the LOB_storage_clause.LONG or LONG RAW column.MOVE an entire partitioned table (either heap or index organized). You must move individual partitions or subpartitions.
The enable_disable_clause lets you specify whether and how Oracle should apply an integrity constraint. The DROP and KEEP clauses are valid only when you are disabling a unique or primary key constraint.
See Also:
|
Oracle permits DDL operations on a table only if the table can be locked during the operation. Such table locks are not required during DML operations.
Specify ENABLE TABLE LOCK to enable table locks, thereby allowing DDL operations on the table.
Specify DISABLE TABLE LOCK to disable table locks, thereby preventing DDL operations on the table.
Use the ALL TRIGGERS clause to enable or disable all triggers associated with the table.
Specify ENABLE ALL TRIGGERS to enable all triggers associated with the table. Oracle fires the triggers whenever their triggering condition is satisfied.
To enable a single trigger, use the enable_clause of ALTER TRIGGER.
| See Also:
CREATE TRIGGER, ALTER TRIGGER, and "Enabling Triggers: Example" |
Specify DISABLE ALL TRIGGERS to disable all triggers associated with the table. Oracle will not fire a disabled trigger even if the triggering condition is satisfied.
The following statement modifies nested table column ad_textdocs_ntab in the sample table sh.print_media so that when queried it returns actual values instead of locators:
ALTER TABLE print_media MODIFY NESTED TABLE ad_textdocs_ntab RETURN AS VALUE;
The following statement specifies parallel processing for queries to the sample table oe.customers:
ALTER TABLE customers PARALLEL;
The following statement places in ENABLE VALIDATE state an integrity constraint named emp_manager_fk in the employees table:
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT emp_manager_fk EXCEPTIONS INTO exceptions;
Each row of the employees table must satisfy the constraint for Oracle to enable the constraint. If any row violates the constraint, then the constraint remains disabled. Oracle lists any exceptions in the table exceptions. You can also identify the exceptions in the employees table with the following statement:
SELECT employees.* FROM employees e, exceptions ex WHERE e.row_id = ex.row_id AND ex.table_name = 'EMPLOYEES' AND ex.constraint = 'EMP_MANAGER_FK';
The following statement tries to place in ENABLE NOVALIDATE state two constraints on the employees table:
ALTER TABLE employees ENABLE NOVALIDATE PRIMARY KEY ENABLE NOVALIDATE CONSTRAINT emp_last_name_nn;
This statement has two ENABLE clauses:
ENABLE NOVALIDATE state.emp_last_name_nn in ENABLE NOVALIDATE state.In this case, Oracle enables the constraints only if both are satisfied by each row in the table. If any row violates either constraint, then Oracle returns an error and both constraints remain disabled.
Consider a referential integrity constraint involving a foreign key on the combination of the areaco and phoneno columns of the phone_calls table. The foreign key references a unique key on the combination of the areaco and phoneno columns of the customers table. The following statement disables the unique key on the combination of the areaco and phoneno columns of the customers table:
ALTER TABLE customers DISABLE UNIQUE (areaco, phoneno) CASCADE;
The unique key in the customers table is referenced by the foreign key in the phone_calls table, so you must use the CASCADE clause to disable the unique key. This clause disables the foreign key as well.
The following example creates the except_table table to hold rows from the index-organized table hr.countries that violate the primary key constraint:
EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE ('hr', 'countries', 'except_table'); ALTER TABLE countries ENABLE PRIMARY KEY EXCEPTIONS INTO except_table;
To specify an exception table, you must have the privileges necessary to insert rows into the table. To examine the identified exceptions, you must have the privileges necessary to query the exceptions table.
The following statement defines and disables a CHECK constraint on the employees table:
ALTER TABLE employees ADD CONSTRAINT check_comp CHECK (salary + (commission_pct*salary) <= 5000) DISABLE;
The constraint check_comp ensures that no employee's total compensation exceeds $5000. The constraint is disabled, so you can increase an employee's compensation above this limit.
The following statement enables all triggers associated with the employees table:
ALTER TABLE employees ENABLE ALL TRIGGERS;
The following statement frees all unused space for reuse in table employees, where the high water mark is above MINEXTENTS:
ALTER TABLE employees DEALLOCATE UNUSED;
The following example renames the credit_limit column of the sample table oe.customers to credit_amount:
ALTER TABLE customers RENAME COLUMN credit_limit TO credit_amount;
This statement illustrates the drop_column_clause with CASCADE CONSTRAINTS. Assume table t1 is created as follows:
CREATE TABLE t1 ( pk NUMBER PRIMARY KEY, fk NUMBER, c1 NUMBER, c2 NUMBER, CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1, CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0), CONSTRAINT ck2 CHECK (c2 > 0) );
An error will be returned for the following statements:
/* The next two statements return errors: ALTER TABLE t1 DROP (pk); -- pk is a parent key ALTER TABLE t1 DROP (c1); -- c1 is referenced by multicolumn -- constraint ck1
Submitting the following statement drops column pk, the primary key constraint, the foreign key constraint, ri, and the check constraint, ck1:
ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;
If all columns referenced by the constraints defined on the dropped columns are also dropped, then CASCADE CONSTRAINTS is not required. For example, assuming that no other referential constraints from other tables refer to column pk, then it is valid to submit the following statement without the CASCADE CONSTRAINTS clause:
ALTER TABLE t1 DROP (pk, fk, c1);
This statement modifies the INITRANS parameter for the index segment of index-organized table hr.countries:
ALTER TABLE countries INITRANS 4;
The following statement adds an overflow data segment to index-organized table countries:
ALTER TABLE countries ADD OVERFLOW;
This statement modifies the INITRANS parameter for the overflow data segment of index-organized table countries:
ALTER TABLE countries OVERFLOW INITRANS 4;
The following statement splits the old partition sales_q4_2000 in the sample table sh.sales, creating two new partitions, naming one sales_q4_2000b and reusing the name of the old partition for the other:
ALTER TABLE sales SPLIT PARTITION SALES_Q4_2000 AT (TO_DATE('15-NOV-2000','DD-MON-YYYY')) INTO (PARTITION SALES_Q4_2000, PARTITION SALES_Q4_2000b);
Assume that the sample table pm.print_media was range partitioned into partitions p1 and p2. (You would have to convert the LONG column in print_media to LOB before partitioning the table.) The following statement splits partition p2 of that table into partitions p2a and p2b:
ALTER TABLE print_media_part SPLIT PARTITION p2 AT (150) INTO (PARTITION p2a TABLESPACE omf_ts1 LOB ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2), PARTITION p2b LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2));
In both partitions p2a and p2b, Oracle creates the LOB segments for columns ad_photo and ad_composite in tablespace omb_ts2. The LOB segments for the remaining columns in partition p2a are stored in tablespace omf_ts1. The LOB segments for the remaining columns in partition p2b remain in the tablespaces in which they resided prior to this ALTER statement. However, Oracle creates new segments for all the LOB data and LOB index segments, even if they are not moved to a new tablespace.
The following statement adds a partition p3 to the print_media_part table (see preceding example) and specifies storage characteristics for the table's BLOB and CLOB columns:
ALTER TABLE print_media_part ADD PARTITION p3 VALUES LESS THAN (MAXVALUE) LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2) LOB (ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE omf_ts1);
The LOB data and LOB index segments for columns ad_photo and ad_composite in partition p3 will reside in tablespace omf_ts2. The remaining attributes for these LOB columns will be inherited first from the table-level defaults, and then from the tablespace defaults.
The LOB data segments for columns ad_source_text and ad_finaltext will reside in the omf_ts1 tablespace, and will inherit all other attributes from the table-level defaults and then from the tablespace defaults.
The following statements use the list partitioned table created in "List Partitioning Example". The first statement splits the existing default partition into a new south partition and a default partition:
ALTER TABLE list_customers SPLIT PARTITION rest VALUES ('MEXICO', 'COLOMBIA') INTO (PARTITION south, PARTITION rest);
The next statement merges the resulting default partition with the asia partition:
ALTER TABLE list_customers MERGE PARTITIONS asia, rest INTO PARTITION rest;
The next statement re-creates the asia partition by splitting the default partition:
ALTER TABLE list_customers SPLIT PARTITION rest VALUES ('CHINA', 'THAILAND') INTO (PARTITION east, partition rest);
The following statement merges back into one partition the partitions created in "Splitting Table Partitions: Examples":
ALTER TABLE sales MERGE PARTITIONS sales_q4_2000, sales_q4_2000b INTO PARTITION sales_q4_2000;
The following statement drops partition p3 created in "Adding a Table Partition with a LOB: Examples":
ALTER TABLE print_media_part DROP PARTITION p3;
The following statement converts partition feb97 to table sales_feb97 without exchanging local index partitions with corresponding indexes on sales_feb97 and without verifying that data in sales_feb97 falls within the bounds of partition feb97:
ALTER TABLE sales EXCHANGE PARTITION feb97 WITH TABLE sales_feb97 WITHOUT VALIDATION;
The following statement marks all the local index partitions corresponding to the nov96 partition of the sales table UNUSABLE:
ALTER TABLE sales MODIFY PARTITION nov96 UNUSABLE LOCAL INDEXES;
The following statement rebuilds all the local index partitions that were marked UNUSABLE:
ALTER TABLE sales MODIFY PARTITION jan97 REBUILD UNUSABLE LOCAL INDEXES;
The following statement changes MAXEXTENTS and logging attribute for partition branch_ny:
ALTER TABLE branch MODIFY PARTITION branch_ny STORAGE (MAXEXTENTS 75) LOGGING;
The following statement moves partition p2b (from "Splitting Table Partitions: Examples") to tablespace omf_ts1:
ALTER TABLE print_media_part MOVE PARTITION p2b TABLESPACE omf_ts1;
The following statement renames a table:
ALTER TABLE employees RENAME TO employee;
In the following statement, partition emp3 is renamed:
ALTER TABLE employee RENAME PARTITION emp3 TO employee3;
The following statement deletes all the data in the sys_p017 partition and deallocates the freed space:
ALTER TABLE deliveries TRUNCATE PARTITION sys_p017 DROP STORAGE;
The following statement splits partition sales_q1_2000 of the sample table sh.sales, and updates any global indexes defined on it:
ALTER TABLE sales SPLIT PARTITION sales_q1_2000 AT (TO_DATE('16-FEB-2000','DD-MON-YYYY')) INTO (PARTITION q1a_2000, PARTITION q1b_2000) UPDATE GLOBAL INDEXES;
The following statements create an object type, a corresponding object table with a primary-key-based object identifier, and a table having a user-defined REF column:
CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30)); CREATE TABLE emp OF emp_t ( empno PRIMARY KEY) OBJECT IDENTIFIER IS PRIMARY KEY; CREATE TABLE dept (dno NUMBER, mgr_ref REF emp_t SCOPE is emp);
The next statements add a constraint and a user-defined REF column, both of which reference table emp:
ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref) REFERENCES emp; ALTER TABLE dept ADD sr_mgr REF emp_t REFERENCES emp;
The following statement adds a column named duty_pct of datatype NUMBER and a column named visa_needed of datatype VARCHAR2 with a size of 3 (to hold "yes" and "no" data) and a CHECK integrity constraint:
ALTER TABLE countries ADD (duty_pct NUMBER(2,2) CHECK (duty_pct < 10.5), visa_needed VARCHAR2(3));
The following statement increases the size of the duty_pct column:
ALTER TABLE countries MODIFY (duty_pct NUMBER(3,2));
Because the MODIFY clause contains only one column definition, the parentheses around the definition are optional.
The following statement changes the values of the PCTFREE and PCTUSED parameters for the employees table to 30 and 60, respectively:
ALTER TABLE employees PCTFREE 30 PCTUSED 60;
The following example modifies the press_release column of the sample table pm.print_media from LONG to CLOB datatype:
ALTER TABLE print_media MODIFY (press_release CLOB);
The following statement allocates an extent of 5 kilobytes for the employees table and makes it available to instance 4:
ALTER TABLE employees ALLOCATE EXTENT (SIZE 5K INSTANCE 4);
Because this statement omits the DATAFILE parameter, Oracle allocates the extent in one of the datafiles belonging to the tablespace containing the table.
This statement modifies the min_price column of the product_information table so that it has a default value of 10:
ALTER TABLE product_information MODIFY (min_price DEFAULT 10);
If you subsequently add a new row to the product_information table and do not specify a value for the min_price column, then the value of the min_price column is automatically 0:
INSERT INTO product_information (product_id, product_name, list_price) VALUES (300, 'left-handed mouse', 40.50); SELECT product_id, product_name, list_price, min_price FROM product_information WHERE product_id = 300; PRODUCT_ID PRODUCT_NAME LIST_PRICE MIN_PRICE ---------- -------------------- ---------- ---------- 300 left-handed mouse 40.5 10
To discontinue previously specified default values, so that they are no longer automatically inserted into newly added rows, replace the values with nulls, as shown in this statement:
ALTER TABLE product_information MODIFY (min_price DEFAULT NULL);
The MODIFY clause need only specify the column name and the modified part of the definition, rather than the entire column definition. This statement has no effect on any existing values in existing rows.
The following example adds a primary key constraint to the xwarehouses table, created in "XMLType Table Examples":
ALTER TABLE xwarehouses ADD (PRIMARY KEY(XMLDATA."WarehouseID"));
| See Also:
XMLDATA for information about this pseudocolumn |
The following statement renames the cust_fname_nn constraint on the sample table oe.customers to cust_firstname_nn:
ALTER TABLE customers RENAME CONSTRAINT cust_fname_nn TO cust_firstname_nn;
The following statement drops the primary key of the departments table:
ALTER TABLE departments DROP PRIMARY KEY CASCADE;
If you know that the name of the PRIMARY KEY constraint is pk_dept, then you could also drop it with the following statement:
ALTER TABLE departments DROP CONSTRAINT pk_dept CASCADE;
The CASCADE clause drops any foreign keys that reference the primary key.
The following statement drops the unique key on the email column of the employees table:
ALTER TABLE employees DROP UNIQUE (email);
The DROP clause in this statement omits the CASCADE clause. Because of this omission, Oracle does not drop the unique key if any foreign key references it.
The following statement adds CLOB column resume to the employee table and specifies LOB storage characteristics for the new column:
ALTER TABLE employees ADD (resume CLOB) LOB (resume) STORE AS resume_seg (TABLESPACE example);
To modify the LOB column resume to use caching, enter the following statement:
ALTER TABLE employees MODIFY LOB (resume) (CACHE);
The following statement adds the nested table column skills to the employee table:
ALTER TABLE employees ADD (skills skill_table_type) NESTED TABLE skills STORE AS nested_skill_table;
You can also modify a nested table's storage characteristics. Use the name of the storage table specified in the nested_table_col_properties to make the modification. You cannot query or perform DML statements on the storage table. Use the storage table only to modify the nested table column storage characteristics.
The following statement creates table vetservice with nested table column client and storage table client_tab. Nested table vetservice is modified to specify constraints:
CREATE TYPE pet_table AS OBJECT (pet_name VARCHAR2(10), pet_dob DATE); CREATE TABLE vetservice (vet_name VARCHAR2(30), client pet_table) NESTED TABLE client STORE AS client_tab; ALTER TABLE client_tab ADD UNIQUE (ssn);
The following statement adds a UNIQUE constraint to nested table nested_skill_table:
ALTER TABLE nested_skill_table ADD UNIQUE (a);
The following statement alters the storage table for a nested table of REF values to specify that the REF is scoped:
CREATE TYPE emp_t AS OBJECT (eno number, ename char(31)); CREATE TYPE emps_t AS TABLE OF REF emp_t; CREATE TABLE emptab OF emp_t; CREATE TABLE dept (dno NUMBER, employees emps_t) NESTED TABLE employees STORE AS deptemps; ALTER TABLE deptemps ADD (SCOPE FOR (column_value) IS emptab);
Similarly, to specify storing the REF with rowid:
ALTER TABLE deptemps ADD (REF(column_value) WITH ROWID);
In order to execute these ALTER TABLE statements successfully, the storage table deptemps must be empty. Also, because the nested table is defined as a table of scalars (REFs), Oracle implicitly provides the column name COLUMN_VALUE for the storage table.
See Also:
|
In the following statement an object type dept_t has been previously defined. Now, create table staff as follows:
CREATE TABLE staff (name VARCHAR(100), salary NUMBER, dept REF dept_t);
An object table offices is created as:
CREATE TABLE offices OF dept_t;
The dept column can store references to objects of dept_t stored in any table. If you would like to restrict the references to point only to objects stored in the departments table, then you could do so by adding a scope constraint on the dept column as follows:
ALTER TABLE staff ADD (SCOPE FOR (dept) IS offices);
The preceding ALTER TABLE statement will succeed only if the staff table is empty.
If you want the REF values in the dept column of staff to also store the rowids, issue the following statement:
ALTER TABLE staff ADD (REF(dept) WITH ROWID);
For examples of defining integrity constraints with the ALTER TABLE statement, see the constraints.
For examples of changing the value of a table's storage parameters, see the storage_clause.
|
 Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|