Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER TABLE to ALTER TABLESPACE, 2 of 3
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.
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, 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.
alter_table::=
alter_table
Groups of ALTER TABLE syntax:
After each clause you will find links to its subclauses if those subclauses do not follow in order immediately below the clause.
alter_table_clauses
physical_attributes_clause
supplemental_lg_grp_clauses::=
supplemental_lg_grp_clauses
allocate_extent_clause
deallocate_unused_clause
upgrade_table_clause
records_per_block_clause
alter_iot_clauses
index_org_table_clause
mapping_table_clauses::=
mapping_table_clauses
compression_clauses::=
compression_clauses
index_org_overflow_clause::=
index_org_overflow_clause
segment_attributes_clause::=
segment_attributes_clause
alter_overflow_clause
add_overflow_clause
alter_mapping_table_clause
alter_table_partitioning
modify_table_default_attrs
modify_table_partition
update_global_index_clause
parallel_clause
partition_attributes::=
partition_attributes
physical_attributes_clause::=
,
allocate_extent_clause::=
,
deallocate_unused_clause::=
,
modify_lob_parameters::=
)
add_table_subpartition
subpartition_attributes::=
subpartition_attributes
modify_table_subpartition
move_table_partition
table_partition_description::=
table_partition_description
segment_attributes_clause::=
,
compression_clauses::=
,
LOB_storage_clause::=
,
varray_col_properties::=
)
partition_level_subpartition::=
partition_level_subpartition
hash_partitioning_storage::=
hash_partitioning_storage
move_table_subpartition
add_range_partition_clause
add_hash_partition_clause
add_list_partition_clause
coalesce_table_partition
drop_table_partition
rename_table_partition
truncate_table_partition
split_table_partition
partition_spec::=
partition_spec
merge_table_partitions
exchange_table_partition
exceptions_clause
row_movement_clause
alter_column_clauses
add_column_options
modify_column_options
drop_column_clause
modify_collection_retrieval::=
modify_collection_retrieval
alter_constraint_clauses
drop_constraint_clause
alter_column_properties
column_properties
object_type_col_properties
substitutable_column_clause::=
substitutable_column_clause
nested_table_col_properties::=
nested_table_col_properties
object_properties::=
object_properties
supplemental_logging_props::=
supplemental_logging_props
physical_properties::=
physical_properties
varray_col_properties
lob_storage_clause
LOB_parameters::=
lob_parameters
partition_storage_clause
modify_lob_storage_clause
modify_lob_parameters::=
modify_lob_parameters
alter_varray_col_properties::=
alter_varray_col_properties
alter_external_table_clause::=
alter_external_table_clause
external_data_properties::=
external_data_properties
move_table_clause
enable_disable_clause
using_index_clause::=
Text description of using_index_clause
global_partitioned_index::=
global_partitioned_index
global_partitioning_clause::=
global_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
, Oracle assumes the table is in your own schema.
Specify the name of the table to be altered.
Restrictions on Temporary Tables
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
or NOLOGGING
, 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
or NOLOGGING.
MOVE.
Restrictions on External Tables:
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
or NOLOGGING
.
MOVE
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.
Restrictions:
PCTUSED
parameter for the index segment of an index-organized table.
PCTUSED
setting. If you alter the PCTFREE
setting, you must subsequently run the DBMS_REPAIR.segment_fix_status
procedure to implement the new setting on blocks already allocated to the segment.
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.
LOGGING
|NOLOGGING
also specifies whether ALTER
TABLE
... MOVE
and ALTER
TABLE
... SPLIT
operations will be logged or not logged.
For a table or table partition, if you omit LOGGING
|NOLOGGING
, the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
For LOBs, if you omit LOGGING
|NOLOGGING
,
CACHE
, then LOGGING
is used (because you cannot have CACHE
NOLOGGING
).
NOCACHE
or CACHE
READS
, the logging attribute defaults to the logging attribute of the tablespace in which the LOB resides.
NOLOGGING
does not apply to LOBs that are stored inline with row data. That is, if you specify NOLOGGING
for LOBs with values less than 4000 bytes and you have not disabled STORAGE
IN
ROW
, Oracle ignores the NOLOGGING
specification and treats the LOB data the same as other table data.
In NOLOGGING
mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose this table, it is important to back up the table after the NOLOGGING
operation.
If the database is run in ARCHIVELOG
mode, media recovery from a backup made before the LOGGING
operation will restore the table. However, media recovery from a backup made before the NOLOGGING
operation will not restore the table.
The logging attribute of the base table is independent of that of its indexes.
See Also:
Oracle9i Data Warehousing Guide for more information about the |
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.
The allocate_extent_clause
lets you explicitly allocate a new extent for the table, the partition or subpartition, the overflow data segment, the LOB data segment, or the LOB index.
Restriction: You cannot allocate an extent for a range- or composite-partitioned table.
Specify the size of the extent in bytes. Use K
or M
to specify the extent size in kilobytes or megabytes. If you omit this parameter, Oracle determines the size based on the values of the STORAGE
parameters of the table's overflow data segment or of the LOB index.
Specify one of the datafiles in the tablespace of the table, overflow data segment, LOB data tablespace, or LOB index to contain the new extent. If you omit this parameter, Oracle chooses the datafile.
Specifying INSTANCE
integer
makes the new extent available to the freelist group associated with the specified instance. If the instance number exceeds the maximum number of freelist groups, the former is divided by the latter, and the remainder is used to identify the freelist group to be used. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER
. If you omit this parameter, the space is allocated to the table but is not drawn from any particular freelist group. Instead, Oracle uses the master freelist and allocates space as needed.
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. You can free only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data).
Oracle credits the amount of the released space to the user quota for the tablespace in which the deallocation occurs.
Oracle deallocates unused space from the end of the object toward the high water mark at the beginning of the object. If an extent is completely contained in the deallocation, then the whole extent is freed for reuse. If an extent is partially contained in the deallocation, then the used part up to the high water mark becomes the extent, and the remaining unused space is freed for reuse.
The exact amount of space freed depends on the values of the INITIAL
, MINEXTENTS
, and NEXT
storage parameters.
Specify the number of bytes above the high water mark that the table, overflow data segment, LOB data segment, or LOB index is to have after deallocation.
KEEP
and the high water mark is above the size of INITIAL
and MINEXTENTS
, then all unused space above the high water mark is freed. When the high water mark is less than the size of INITIAL
or MINEXTENTS
, then all unused space above MINEXTENTS
is freed.
KEEP
, then the specified amount of space is kept and the remaining space is freed. When the remaining number of extents is less than MINEXTENTS
, then MINEXTENTS
is adjusted to the new number of extents. If the initial extent becomes smaller than INITIAL
, then INITIAL
is adjusted to the new size.
NEXT
is set to the size of the last extent that was deallocated.
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.
Restriction: 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.) NOCACHE
is the default for LOB storage.
Restriction: 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 Guide and Reference for more information on using this clause |
Specify NOMONITORING
if you do not want Oracle to collect modification statistics on table
.
Restriction: 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.
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
partition_storage_clause
. 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.
Restriction: 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.
Restrictions:
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.
Restriction: 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
.
Restriction: 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. |
See index_org_table_clause
in
CREATE
TABLE
on .
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.
Restriction: 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.
Restriction: You cannot specify this clause for individual partitions of an index-organized table.
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.
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
, 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, Oracle uses the tablespace specified for the table. If you do not specify TABLESPACE
at the table level, Oracle uses the tablespace of the partition's primary key index segment.
The alter_mapping_table_clause
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.
Specify the allocate_extent_clause
to allocate new extents at the end of the mapping table for the index-organized table.
Specify the deallocate_unused_clause
to deallocate unused space at the end of the mapping table of the index-organized table.
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 following clauses apply only to partitioned tables. You cannot combine partition operations with other partition operations or with operations on the base table in one ALTER
TABLE
statement.
Notes:
|
The modify_table_default_attrs
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
, COMPRESS
, and OVERFLOW
clauses are valid only for partitioned index-organized tables.
PCTUSED
parameter for the index segment of an index-organized table.
COMPRESS
only if compression is already specified at the table level.
The modify_table_partition
clause lets you change the real physical attributes of partition
. This clause optionally modifies the storage attributes of one or more LOB items for the partition. You can specify new values for any of the following physical attributes for the partition: the logging attribute; PCTFREE
, PCTUSED
, INITRANS
, or MAXTRANS
parameter; or storage parameters.
If table
is composite partitioned:
allocate_extent_clause
, Oracle allocates an extent for each subpartition of partition
.
deallocate_unused_clause
, 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.
Restriction: If table
is hash partitioned, 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.
The add_table_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.
If you do not specify subpartition
, Oracle assigns a name in the form SYS_SUBP
nnn
If you do not specify TABLESPACE
, 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.
Restriction: You cannot specify this clause for a list partition.
COALESCE
applies only to hash partitions and subpartitions. Use the COALESCE
SUBPARTITION
clause if you want Oracle to select a hash subpartition, distribute its contents into one or more remaining subpartitions (determined by the hash function), and then drop the selected 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.
These clauses are valid only when you are modifying list partitions.
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, Oracle returns an error.
The next two clauses modify the attributes of local index partitions corresponding to partition
.
UNUSABLE
LOCAL
INDEXES
marks UNUSABLE
all the local index partitions associated with partition
.
REBUILD
UNUSABLE
LOCAL
INDEXES
rebuilds the unusable local index partitions associated with partition
.
Restrictions:
modify_table_partition_clause
.
When you perform DDL on a table partition, if a global index is defined on table
, 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, Oracle invalidates the global indexes.
Restrictions: 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
.
Restrictions:
table
contains any columns of LOB or user-defined object type, 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
, the parallelism applies only to the move, not to subsequent DML and query operations on the table.
The modify_table_subpartition
clause lets you allocate or deallocate storage for an individual subpartition of table
.
UNUSABLE
LOCAL
INDEXES
marks UNUSABLE
all the local index subpartitions associated with subpartition
.
REBUILD
UNUSABLE
LOCAL
INDEXES
rebuilds the unusable local index subpartitions associated with subpartition
.
Restrictions:
modify_lob_parameters
you can specify for subpartition are the allocate_extent_clause
and deallocate_unused_clause
.
UNUSABLE
LOCAL
INDEXES
clauses for list partitions.
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, Oracle assigns the same name to the corresponding primary key index partition as well as to any existing overflow partitions and mapping table partitions.
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, 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, 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 selected partition. If the moved partitions are not empty, 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, Oracle performs the move without using parallelism.
Specifying the parallel_clause
in MOVE
PARTITION
does not change the default parallel attributes of table
.
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
.
Restrictions on Moving a Table Partition:
partition
is a hash partition, 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
, 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, Oracle marks
UNUSABLE
, and you must rebuild, all local index subpartitions corresponding to the subpartition being moved.
If the table contains LOB columns, you can use the LOB_storage_clause
to move the LOB data and LOB index segments associated with this subpartition. Only the LOBs named are affected. If you do not specify the LOB_storage_clause
for a particular LOB column, 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.
Restriction: You cannot move a subpartition of a list partition.
Use the ADD
PARTITION
clauses 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, Oracle generates a partition name of the form SYS_P
n
.
If table
is index organized, 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, you can also specify partition-level attributes for one or more LOB items.
If a domain index is defined on table
, the index must not be marked IN_PROGRESS
or FAILED
.
A table can have up to 64K-1 partitions.
Restrictions:
MAXVALUE
, 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.
compression_clause
and OVERFLOW
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 compression only if 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-separated, 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 is permitted only for a composite-partitioned table. This clause lets you specify hash subpartitions for a new range or list partition. You specify composite partitioning in one of two ways:
SYS_SUBP
nnn
. The number of tablespaces does not have to equal the number of subpartitions. If the number of subpartitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.
The subpartitions inherit all their attributes from any attributes specified for the new partition, except for TABLESPACE
, which you can specify at the subpartition level. Any attributes not specified at the subpartition or partition level are inherited from table-level defaults.
This clause overrides any subpartitioning specified at the table level.
If you do not specify this clause but you specified default subpartitioning at the table level, the new partition inherits the table-level default subpartitioning.
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.
You can specify a name for the partition, and optionally a tablespace where it should be stored. If you do not specify a name, Oracle assigns a partition name of the form SYS_P
nnn
. If you do not specify TABLESPACE
, 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, 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.
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, 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.
COALESCE
applies only to hash partitions and subpartitions. Use the coalesce_table_partition
clause to indicate that Oracle should select a hash partition, distribute its contents into one or more remaining partitions (determined by the hash function), and then drop the selected 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, you must merge the partition into one of the adjacent partitions.
If the table has LOB columns, 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, 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
, Oracle parallelizes the index update, not the drop operation.
If you drop a partition and later insert a row that would have belonged to the dropped partition, Oracle stores the row in the next higher partition. However, if that partition is the highest partition, the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.
Restrictions:
table
contains only one partition, you cannot drop the partition. You must drop the table.
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, Oracle also truncates any corresponding mapping table partitions and overflow area partitions.
If the partition or subpartition to be truncated contains data, 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, the LOB data and LOB index segments for this partition are also truncated. If table
is composite partitioned, the LOB data and LOB index segments for this partition's subpartitions are truncated.
If a domain index is defined on table
, 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
, 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
, Oracle parallelizes the index update, not the truncate operation.
Restriction: You cannot truncate a subpartition of a list partition.
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.
Restriction: You cannot specify this clause for a hash-partitioned table.
If you specify subpartitioning for the new partitions, you can specify only TABLESPACE
for the subpartitions. All other attributes are inherited from the containing new partition.
If current_partition
is subpartitioned and you do not specify any subpartitioning for the new partitions, the new partitions inherit the number and tablespaces of the subpartitions in current_partition
.
If table is index organized, 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.
Oracle splits corresponding local index partitions, even if they are 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.
Oracle drops local index partitions corresponding to the selected partition. Oracle marks UNUSABLE
, and you must rebuild, the local index partitions corresponding to the split partitions.
If table
contains LOB columns, 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
(value_list)
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
(value_list
) 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
. The new partitions inherit all unspecified physical attributes from current_partition
.
Oracle splits the corresponding partition in each local index defined on table
, even if the index is marked UNUSABLE
.
Restriction: 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. 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, Oracle assigns names of the form SYS_P
n
. Any attributes you do not specify are inherited from current_partition
.
Restrictions:
compression_clause
and OVERFLOW
only for a partitioned index-organized table.
PCTUSED
parameter for the index segment of an index-organized table.
The parallel_clause
lets you parallelize the split operation, but does not change the default parallel attributes of the table.
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. The two partitions to be merged must be adjacent if they are range partitions. List partitions need not be adjacent in order to be merged.
Restriction: You cannot specify this clause for a hash-partitioned table.
The new partition inherits the partition-bound of the higher of the two original partitions.
Any attributes not specified in the segment_attributes_clause
are inherited from table-level defaults.
If you do not specify a new partition_name
, Oracle assigns a name of the form SYS_P
nnn
. If the new partition has subpartitions, Oracle assigns subpartition names of the form SYS_SUBP
nnn
.
When you merge two list partitions, the resulting partition_value
list is the union of the set of the two partition_value
lists of the partitions being merged.
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 partitions. Oracle marks UNUSABLE
, and you must rebuild, the local index partitions corresponding to merged partition.
The partition_level_subpartition
clause lets you specify hash subpartitioning attributes for the new merged partition. Any attributes not specified in this clause are inherited from table-level defaults. If you do not specify this clause, the new merged partition inherits subpartitioning attributes from table-level defaults.
The parallel_clause
lets you parallelize the merge operation.
Use the EXCHANGE
PARTITION
or EXCHANGE
SUBPARTITION
clause to exchange the data and index segments of
This clause facilitates high-speed data loading when used with transportable tablespaces.
If table
contains LOB columns, 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, 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
, 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 should place the rowids of all rows violating the constraint. If you omit schema
, Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named EXCEPTIONS
. The exceptions table must be on your local database.
You can create the EXCEPTIONS
table using one of these scripts:
UTLEXCPT.SQL
uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)
UTLEXPT1.SQL
uses universal rowids, so it can accommodate rows from both heap-organized and index-organized tables.
If you create your own exceptions table, it must follow the format prescribed by one of these two scripts.
See Also:
|
Restrictions on the exceptions_clause
UNIQUE
constraint, and that constraint must be in DISABLE
VALIDATE
state.
If these conditions are not true, Oracle ignores this clause.
Restrictions on Exchanging Partitions
The row_movement_clause
determines whether a row can be moved to a different partition or subpartition because of a change to one or more of its key values.
Restriction: You can specify this clause only for partitioned tables.
Specify ENABLE
to allow Oracle to move a row to a different partition or subpartition as the result of an update to the partitioning or subpartitioning key.
Specify DISABLE
to have Oracle return an error if an update to a partitioning or subpartitioning key would result in a row moving to a different partition or subpartition. This is the default.
ADD
add_column_options
lets you add a column or integrity constraint to a table.
If you add a column, 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
, 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.
Restrictions:
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, 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.
Restrictions:
DEFAULT
expression cannot contain references to other columns, the pseudocolumns CURRVAL
, NEXTVAL
, LEVEL
, and ROWNUM
, or date constants that are not fully specified.
These clauses let you further describe a column of type REF
. The only difference between these clauses is that you specify table_ref
from the table level, so you must identify the REF
column or attribute you are defining. You specify column_ref
after you have already identified the REF
column or attribute.
Use column_constraint
to add or remove a NOT
NULL
constraint to or from an existing column. You cannot use this clause to modify any other type of constraint using ALTER
TABLE
.
Use table_or_view_constraint
to add or modify an integrity constraint on the table.
Use MODIFY
modify_column_options
to modify the definition of an existing column. Any of the optional parts of the column definition (datatype, default value, or column 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, 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, 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, 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, you must do so in a subsequent ALTER
TABLE
statement.
LONG
column, you must drop them before modifying the column to a LOB.
See Also:
LONG
and LOB columns
LONG
to LOB migration
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 with the column constraint syntax 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, using the ADD
clause and the table constraint syntax.
Restrictions:
ROWID
for an index-organized table, but you can specify a column of type UROWID
.
REF
.
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, only the locators stored in that column are removed, not the files referenced by the locators.
INCLUDING
column, 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
.
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.
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, 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.
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, 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, it cannot contain duplicates.
Specify CASCADE
CONSTRAINTS
if you want to drop all referential integrity 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.
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, Oracle applies a checkpoint after all the rows have been processed. If you do not specify integer
, 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, 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 below).
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.
Restrictions on the drop_column_clause
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
.
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.
Use the alter_constraint_clauses
to modify the state of a constraint or to drop a constraint.
MODIFY
CONSTRAINT
lets you change the state of an existing constraint.
Restrictions on Modifying Constraints
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, 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_column_options
only when you are changing a LONG
or LONG
RAW
column to a LOB
.
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, 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.
Restrictions on the drop_constraint_clause:
CASCADE
clause. If you omit CASCADE
, 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, the REF
column remains scoped to the referenced table.
The alter_column_properties
clause lets you change the storage characteristics of a column or partition.
The column_properties
determine the storage characteristics of an object, nested table, varray, or LOB column.
Use the object_type_col_properties
to specify storage characteristics of an 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
.
Restrictions on the substitutable_column_clause:
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.
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.
Restrictions:
parallel_clause
.
TABLESPACE
(as part of the segment_attributes_clause
) for a nested table. The tablespace is always that of the parent table.
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, Oracle will always store the varray in a LOB, even if it is small enough to be stored inline.
Restriction: 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. You cannot use this clause to modify an existing LOB column. 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 the LOGGING
or NOLOGGING
, 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
, Oracle retains the existing values of the LOB attributes.
Restrictions:
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.
Restriction: You cannot change STORAGE
IN
ROW
once it is set. Therefore, you cannot specify this clause as part of the modify_column_options
clause. However, you can change this setting when adding a new column (add_column_options
) 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, Oracle rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and integer
is 2050, Oracle allocates 4096 bytes (2 blocks).The maximum value is 32768 (32 K), which is the largest Oracle block size allowed. The default CHUNK
size is one Oracle database block.
Restrictions:
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
, Oracle returns an error.
Specify the maximum percentage of overall LOB storage space to be used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
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 for information on how Oracle manages LOB indexes in tables migrated from earlier versions |
The 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, 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, Oracle stores the LOB data partition in the same tablespace as the table partition to which it corresponds.
Restriction: You can specify only one list of partition_storage_clauses
in a single ALTER
TABLE
statement, and all LOB_storage_clauses
and varray_col_properties
clause must precede the list of partition_storage_clauses
.
The modify_lob_storage_clause
lets you change the physical attributes of the LOB lob_item
. You can specify only one lob_item
for each modify_lob_storage_clause
.
Restrictions:
INITIAL
parameter in the storage_clause
when modifying the LOB storage attributes.
allocate_extent_clause
and the deallocate_unused_clause
in the same statement.
The alter_varray_col_properties
clause lets you change the storage characteristics of an existing LOB in which a varray is stored.
Restriction: 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 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
.
Restrictions:
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 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, 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.
Restrictions:
MOVE
. If you specify ONLINE
and then issue parallel DML statements, 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, 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.
Restriction: You cannot specify NOMAPPING
if any bitmapped indexes have been defined on table.
Use the 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.
Restrictions on the move_table_clause
MOVE
, 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.
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 DML 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
.
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 demo 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;
PARALLEL
Example
The following statement specifies parallel processing for queries to the demo table oe.customers
:
ALTER TABLE customers PARALLEL;
ENABLE
VALIDATE
Example
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 except_table;
Each row of the employees
table must satisfy the constraint for Oracle to enable the constraint. If any row violates the constraint, the constraint remains disabled. Oracle lists any exceptions in the table except_table
. You can also identify the exceptions in the employees
table with the following statement:
SELECT employees.* FROM employees e, except_table ex WHERE e.row_id = ex.row_id AND ex.table_name = 'EMPLOYEES' AND ex.constraint = 'EMP_MANAGER_FK';
EXCEPTIONS
INTO
for Index-Organized Tables Example
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.
ENABLE
NOVALIDATE
Example
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, 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.
CHECK
Constraint Example
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 CONSTRAINT check_comp;
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 emp
table:
ALTER TABLE employees ENABLE ALL TRIGGERS;
DEALLOCATE
UNUSED
Example
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;
DROP
COLUMN
Example
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:
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;
ADD
PARTITION
Example
The following statement adds a partition p3
and specifies storage characteristics for three of the table's LOB columns (b
, c
, and d
):
ALTER TABLE pt ADD PARTITION p3 VALUES LESS THAN (30) LOB (b, d) STORE AS (TABLESPACE tsz) LOB (c) STORE AS mylobseg;
The LOB data and LOB index segments for columns b
and d
in partition p3
will reside in tablespace tsz
. 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 column c
will reside in the mylobseg
segment, and will inherit all other attributes from the table-level defaults and then from the tablespace defaults.
SPLIT
PARTITION
Example
The following statement splits partition p3
of table pt
into partitions p3_1
and p3_2
:
ALTER TABLE pt SPLIT PARTITION p3 AT (25) INTO (PARTITION p3_1 TABLESPACE ts4 LOB (b,d) STORE AS (TABLESPACE tsz), PARTITION p3_2 (TABLESPACE ts5) LOB (c) STORE AS (TABLESPACE ts5);
In partition p3_1
, Oracle creates the LOB segments for columns b
and d
in tablespace tsz
. In partition p3_2
, Oracle creates the LOB segments for column c
in tablespace ts5
. The LOB segments for columns b
and d
in partition p3_2
and those for column c
in partition p3_1
remain in original tablespace for the original partition p3
. However, Oracle creates new segments for all the LOB data and LOB index segments, even though they are not moved to a new tablespace.
The following statement splits partition sales_q1_2000
of the demo 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 demo table pm.print_media
from LONG
to CLOB
datatype:
ALTER TABLE print_media MODIFY (press_release CLOB);
ALLOCATE
EXTENT
Example
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, 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 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
, 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 resume_ts);
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 (REF
s), 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 emp
as follows:
CREATE TABLE emp (name VARCHAR(100), salary NUMBER, dept REF dept_t);
An object table DEPARTMENTS
is created as:
CREATE TABLE departments 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, you could do so by adding a scope constraint on the dept
column as follows:
ALTER TABLE emp ADD (SCOPE FOR (dept) IS departments);
The above ALTER
TABLE
statement will succeed only if the emp
table is empty.
If you want the REF
values in the dept
column of emp
to also store the rowids, issue the following statement:
ALTER TABLE emp ADD (REF(dept) WITH ROWID);
The following statement adds partition jan99
to tablespace tsx
:
ALTER TABLE sales ADD PARTITION jan99 VALUES LESS THAN( '970201' ) TABLESPACE tsx;
The following statement drops partition dec98
:
ALTER TABLE sales DROP PARTITION dec98;
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 depot2
to tablespace ts094
:
ALTER TABLE parts MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;
The following statement renames a table:
ALTER TABLE emp RENAME TO employee;
In the following statement, partition emp3
is renamed:
ALTER TABLE employee RENAME PARTITION emp3 TO employee3;
The following statement splits the old partition depot4
, creating two new partitions, naming one depot9
and reusing the name of the old partition for the other:
ALTER TABLE parts SPLIT PARTITION depot4 AT ( '40-001' ) INTO ( PARTITION depot4 TABLESPACE ts009 STORAGE (MINEXTENTS 2), PARTITION depot9 TABLESPACE ts010 ) PARALLEL (10);
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;
For examples of defining integrity constraints with the ALTER
TABLE
statement, see the constraint_clause.
For examples of changing the value of a table's storage parameters, see the .
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|