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