Oracle8i SQL Reference
Release 3 (8.1.7)
Part Number A85397-01

Library
Product
Contents
Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
ALTER TABLE to constraint_clause, 2 of 14

ALTER TABLE

Purpose

Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition.

Additional Topics:

Prerequisites

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.

Additional Prerequisites for Partitioning Operations

In addition, if you are not the owner of the table, you need the DROP ANY TABLE privilege in order to use the drop_partition_clause or truncate_partition_clause.

You must also have space quota in the tablespace in which space is to be acquired in order to use the add_partition_clause, modify_partition_clause, move_partition_clause, and split_partition_clause.

Additional Prerequisites for Constraints and Triggers

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

To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.

Additional Prerequisites When Using Object Types

To use an object type in a column definition when modifying a table, either that object must belong to the same schema as the table being altered, or you must have either the EXECUTE ANY TYPE system privilege or the EXECUTE schema object privilege for the object type.

See Also: CREATE INDEX for information on the privileges needed to create indexes 

Syntax



add_column_options::=


column_constraint, table_constraint, column_ref_constraint, table_ref_constraint, constraint_state: See the constraint_clause.

LOB_storage_clause::=


LOB_parameters::=


storage_clause: See storage_clause.

varray_storage_clause::=


nested_table_storage_clause::=


object_properties::=


physical_properties::=


partition_storage_clause::=


modify_column_options::=


move_table_clause::=


segment_attributes_clause::=


physical_attributes_clause::=


index_organized_table_clause::=


compression_clause::=


index_organized_overflow_clause::=


modify_collection_retrieval_clause::=


modify_storage_clauses::=


modify_LOB_storage_clause::=


modify_LOB_storage_parameters::=


allocate_extent_clause::=


deallocate_unused_clause::=


modify_varray_storage_clause::=


drop_constraint_clause::=


drop_column_clause::=


records_per_block_clause::=


alter_overflow_clause::=


overflow_clause::=


add_overflow_clause::=


partitioning_clauses::=


modify_default_attributes_clause::=


modify_partition_clause::=


partition_attributes::=


add_subpartition_clause::=


subpartition_description::=


modify_subpartition_clause::=


move_partition_clause::=


table_partition_description::=


partition_level_subpartitioning::=


hash_partitioning_storage_clause::=


move_subpartition_clause::=



add_range_partition_clause::=


add_hash_partition_clause::=


coalesce_partition_clause::=


drop_partition_clause::=


rename_partition/ subpartition_clause::=


truncate_partition_clause and truncate_subpartition_clause::=


split_partition_clause::=


merge_partitions_clause::=


partition_spec::=


exchange_partition_clause and exchange_subpartition_clause::=


row_movement_clause::=


parallel_clause::=


enable_disable_clause::=


using_index_clause::=


Keywords and Parameters

The clauses described below have specialized meaning in the ALTER TABLE statement. For descriptions of the remaining keywords, see CREATE TABLE.

Note: Operations performed by the ALTER TABLE statement can cause Oracle to invalidate procedures and stored functions that access the table. For information on how and when Oracle invalidates such objects, see Oracle8i Concepts. 

schema

Specify the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.

table

Specify the name of the table to be altered.

You can modify, or drop columns from, or rename a temporary table. However, for a temporary table, you cannot:

add_column_options

ADD add_column_options lets you add a column or integrity constraint.

See Also: CREATE TABLE for a description of the keywords and parameters of this clause 

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.

See Also: CREATE VIEW 

Restrictions:

LOB_storage_clause

Use the LOB_storage_clause to specify the LOB storage characteristics for the newly added LOB column. You cannot use this clause to modify an existing LOB column. Instead, you must use the modify_LOB_storage_clause.

Restrictions:

varray_storage_clause

The varray_storage_clause lets you specify separate storage characteristics for the LOB in which a varray will be stored. In addition, 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 the TABLESPACE clause of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the containing table's tablespace.

nested_table_storage_clause

the nested_table_storage_clause lets you specify separate storage characteristics for a nested table, which in turn enables 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.)

Restrictions:

partition_storage_clause

The partition_storage_clause lets you specify a separate LOB_storage_clause or varray_storage_clause for each partition. You must specify the partitions in the order of partition position.

If you do not specify a LOB_storage_clause or varray_storage_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 at the table level for the LOB item, 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 per ALTER TABLE statement, and all LOB_storage_clauses and varray_storage_clauses must precede the list of partition_storage_clauses.

modify_column_options

Use MODIFY modify_column_options to modify the definition of an existing column. If you omit any of the optional parts of the column definition (datatype, default value, or column constraint), these parts remain unchanged.

Restrictions:

move_table_clause

For a heap-organized table, use the segment_attributes_clause of the syntax. 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. (LOB items not specified in this clause are not moved.)

For an index-organized table, use the index_organized_table_clause of the syntax. The move_table_clause rebuilds the index-organized table's primary key index B*-tree. The overflow data segment is not rebuilt unless the OVERFLOW keyword is explicitly stated, with two exceptions:

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.

ONLINE 

Specify ONLINE if you want DML operations on the index-organized table to be allowed during rebuilding of the table's primary key index B*-tree.

Restrictions:

  • You can specify this clause only for a nonpartitioned index-organized table.

  • Parallel DML is not supported during online MOVE. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.

 

compression_clause 

Use the compression_clause to enable and 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.

    Restrictions:

    - You can specify this clause only for an index-organized table.

    - You can specify compression for a partition of an index-organized table only if compression has been specified at the table level.

 

 
  • NOCOMPRESS disables key compression in index-organized tables. This is the default.

 

TABLESPACE tablespace 

Specify the tablespace into which the rebuilt index-organized table is stored. 

Restrictions on the move_table_clause:

physical_attributes_clause

The physical_attributes_clause lets you change the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and storage characteristics.

Restriction: You cannot specify the PCTUSED parameter for the index segment of an index-organized table.

See Also: The PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of CREATE TABLE and the  

Cautions:

  • For a nonpartitioned table, the values you specify override any values specified for the table at create time.

  • For a range- or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_default_attributes_clause.

  • For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_default_attributes_clause with the FOR PARTITION clause.

 

modify_collection_retrieval_clause

Use the modify_collection_retrieval_clause to change what is returned when a collection item is retrieved from the database.

collection_item 

Specify the name of a column-qualified attribute whose type is nested table or varray. 

RETURN AS 

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.

 

modify_storage_clauses

modify_LOB_storage_clause 

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:

  • You cannot modify the value of the INITIAL parameter in the storage_clause when modifying the LOB storage attributes.

  • You cannot specify both the allocate_extent_clause and the deallocate_unused_clause in the same statement.

 

modify_varray_storage_clause 

The modify_varray_storage_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 containing table's tablespace. 

drop_constraint_clause

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.

PRIMARY KEY 

Specify PRIMARY KEY to drop the table's PRIMARY KEY constraint. 

UNIQUE 

Specify UNIQUE to drop the UNIQUE constraint on the specified columns. 

CONSTRAINT constraint 

Specify the integrity constraint you want dropped. 

CASCADE 

Specify CASCADE if you want all other integrity constraints that depend on the dropped integrity constraint to be dropped as well. 

Restrictions on the drop_constraint_clause:

drop_column_clause

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.

Restrictions on the drop_column_clause:

allocate_extent_clause

The allocate_extent_clause lets you explicitly allocates 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.

Note: Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.  

SIZE integer 

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.  

DATAFILE 'filename' 

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.  

INSTANCE integer 

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. Rather, the master freelist is used, and space is allocated as needed.  

 

Note: Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.

 

See Also: Oracle8i Concepts  

deallocate_unused_clause

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

See Also: for a description of these parameters 

KEEP integer 

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.  

 
  • If you omit 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.

 

 
  • If you specify 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.

 

 
  • In either case, NEXT is set to the size of the last extent that was deallocated.

 

CACHE | NOCACHE

CACHE 

For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the 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. 

NOCACHE 

For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed.  

 

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. 

CACHE READS 

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.

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

MONITORING | NOMONITORING

MONITORING 

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: Oracle8i Performance Guide and Reference for more information on using this clause

 

NOMONITORING 

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. 

LOGGING | NOLOGGING

LOGGING | NOLOGGING 

Specify whether subsequent Direct Loader (SQL*Loader) and direct-load 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_default_attributes_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,

  • If you specify CACHE, then LOGGING is used (because you cannot have CACHE NOLOGGING).

  • If you specify NOCACHE or CACHE READS, the logging attribute defaults to the logging attribute of the tablespace in which it 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 take a backup after the NOLOGGING operation.  

 

If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the LOGGING operation will restore the table. However, media recovery from a backup taken 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: Oracle8i Parallel Server Concepts for more information about the logging_clause and parallel DML

 

RENAME TO

RENAME TO  

Use the RENAME clause to rename table to new_table_name. 

 

Note: Using this clause will invalidate any dependent materialized views.

See Also: CREATE MATERIALIZED VIEW and Oracle8i Data Warehousing Guide for more information on materialized views

 

records_per_block_clause

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:

alter_overflow_clause

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.

Note: When you alter an index-organized table, Oracle evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified OVERFLOW, Oracle raises an error and does not execute the ALTER TABLE statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking. 

PCTTHRESHOLD integer 

Specify the percentage of space reserved in the index block for an index-organized table row. 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.

Restrictions:

  • PCTTHRESHOLD must be large enough to hold the primary key.

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

    See Also: the INCLUDING clause of the index_organized_table_clause

 

INCLUDING column_name 

Specify a column at which to divide an index-organized table row into index and overflow portions. The primary key columns are always stored in the index. column_name can be either the last primary-key column or any non-primary-key column. All non-primary-key columns that follow column_name are stored in the overflow data segment.

Restriction: You cannot specify this clause for individual partitions of an index-organized table. 

 

Note: If an attempt to divide a row at column_name causes the size of the index portion of the row to exceed the PCTTHRESHOLD value (either specified or default), Oracle breaks up the row based on the PCTTHRESHOLD value.

 

overflow_clause 

The overflow_clause lets 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.

Restriction: You cannot specify OVERFLOW for a partition of a partitioned index-organized table unless the table already has an overflow segment.

See Also: CREATE TABLE

 

add_overflow_clause 

The add_overflow_clause lets you add an overflow data segment to the specified index-organized table.

For a partitioned index-organized table:

  • If you do not specify PARTITION, Oracle automatically allocates an overflow segment for each partition. The physical attributes of these segments are inherited from the table level.

  • If you wish to specify separate physical attributes for one or more partitions, you must specify such attributes for every partition in the table. You do not specify the name of the partitions, but you must specify their attributes in the order in which they were created.

You can find the order of the partitions by querying the PARTITION_NAME and PARTITION_POSITION columns of the USER_IND_PARTITIONS view.

If you do not specify TABLESPACE for a particular partition, 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. 

partitioning_clauses

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.

Note: If you drop, exchange, truncate, move, modify, or split a partition on a table that is a master table for one or more materialized views, existing bulk load information about the table will be deleted. Therefore, be sure to refresh all dependent materialized views before performing any of these operations.  

modify_default_attributes_clause

The modify_default_attributes_clause lets you specify new default values for the attributes of table. Partitions and LOB partitions you create subsequently will inherit these values unless you override them explicitly when creating the partition or LOB partition. Existing partitions and LOB partitions are not affected by this clause.

Only attributes named in the statement are affected, and the default values specified are overridden by any attributes specified at the individual partition level.

FOR PARTITION 

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. 

Restrictions:

modify_partition_clause

The modify_partition_clause lets you change the real physical attributes of the partition table partition. 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:

Restriction: If table is hash partitioned, you can specify only the allocate_extent and deallocate_unused clauses. 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.

add_subpartition_clause 

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

Oracle marks UNUSABLE, and you must rebuild, the local index subpartitions corresponding to the added and to the rehashed subpartitions. 

 

If you do not specify subpartition, Oracle assigns a name in the form SYS_SUBPnnn

If you do not specify TABLESPACE, the new subpartition will reside in the default tablespace of partition. 

COALESCE SUBPARTITION 

Specify COALESCE PARTITION 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.

Local index subpartitions corresponding to the selected subpartition are also dropped. Oracle marks UNUSABLE, and you must rebuild, the index subpartitions corresponding to one or more absorbing subpartitions. 

UNUSABLE LOCAL INDEXES 

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:

  • You cannot specify this clause with any other clauses of the modify_partition_clause.

  • You cannot specify this clause for partitions that are subpartitioned.

 

modify_subpartition_clause

The modify_subpartition_clause lets you allocate or deallocate storage for an individual subpartition of table.

Restriction: The only modify_LOB_storage_parameters you can specify for subpartition are the allocate_extent_clause and deallocate_unused_clause.

rename_partition/ subpartition_clause

Use the rename_partition_clause or rename_subpartition_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.

move_partition_clause

Use the move_partition_clause to move table partition 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.

If partition is not empty, MOVE PARTITION marks UNUSABLE all corresponding local index partitions and all global nonpartitioned indexes, and all the partitions of global partitioned indexes.

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.

The parallel_clause on MOVE PARTITION does not change the default parallel attributes of table.

Note: For index-organized tables, Oracle uses the address of the primary key, as well as its value, to construct logical rowids. The logical rowids are stored in the secondary index of the table. If you move a partition of an index-organized table, the address portion of the rowids will change, which can hamper performance. To ensure optimal performance, rebuild the secondary index(es) on the moved partition to update the rowids.  

See Also: Oracle8i Concepts for more information on logical rowids 

Restrictions:

move_subpartition_clause

Use the move_subpartition_clause to move the table subpartition subpartition to another segment. If you do not specify TABLESPACE, the subpartition will remain in the same tablespace.

Unless the subpartition is empty, Oracle marks UNUSABLE all local index subpartitions corresponding to the subpartition being moved, as well as global nonpartitioned indexes and partitions of global indexes.

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.

add_range_partition_clause

The add_range_partition_clause lets you add a new range partition 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.

You can specify up to 64K-1 partitions.

See Also: Oracle8i Administrator's Guide for a discussion of factors that might impose practical limits less than this number 

Restrictions:

add_hash_partition_clause

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 new_partition_name, Oracle assigns a partition name of the form SYS_Pnnn. 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.

See Also: CREATE TABLE and Oracle8i Concepts for more information on hash partitioning 

parallel_clause 

lets you specify whether to parallelize the creation of the new partition. 

coalesce_partition_clause

COALESCE applies only to hash-partitioned tables. This clause specifies 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. Local index partitions corresponding to the selected partition are also dropped. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to one or more absorbing partitions.

drop_partition_clause

The drop_partition_clause applies only to tables partitioned using the range or composite method. This clause removes partition 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.

See Also: merge_partitions_clause 

If the table has LOB columns, the LOB data and LOB index partitions (and their subpartitions, if any) corresponding to partition are also dropped.

Restriction: If table contains only one partition, you cannot drop the partition. You must drop the table.

truncate_partition_clause and truncate_subpartition_clause

TRUNCATE PARTITION removes all rows from partition or, if the table is composite-partitioned, all rows from partition's subpartitions. TRUNCATE SUBPARTITION removes all rows from subpartition.

If the table contains any LOB columns, the LOB data and LOB index segments for this partition are also truncated. If the table is composite-partitioned, the LOB data and LOB index segments for this partition's subpartitions are truncated.

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.

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. In addition, if the truncated partition or subpartition, or any of the subpartitions of the truncated partition are not empty, Oracle marks as UNUSABLE all global nonpartitioned indexes and partitions of global indexes defined on the table.

DROP STORAGE 

Specify DROP STORAGE to deallocate space from the deleted rows and make it available for use by other schema objects in the tablespace. 

REUSE STORAGE 

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. 

split_partition_clause

The split_partition_clause lets you create, from an original partition partition_name_old, two new partitions, each with a new segment and new physical attributes, and new initial extents. The segment associated with partition_name_old is discarded.

Restriction: You cannot specify this clause for a hash-partitioned table.

AT (value_list) 

Specify the new noninclusive upper bound for split_partition_1. The value_list must compare less than the original partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one). 

INTO

partition_spec, partition_spec 

The INTO clause lets you describe the two partitions resulting from the split. The keyword PARTITION is required. Specify 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_Pn. Any attributes you do not specify are inherited from partition_name_old. 

 

Restriction:

  • You can specify the compression_clause, physical_attributes_clause, and OVERFLOW only for a partitioned index-organized table.

  • You cannot specify the PCTUSED parameter for the index segment of an index-organized table.

 

 

parallel_clause 

The parallel_clause lets you parallelize the split operation, but does not change the default parallel attributes of the table. 

If you specify subpartitioning for the new partitions, you can specify only TABLESPACE for the subpartitions. All other attributes will be inherited from the containing new partition.

If partition_name_old is subpartitioned, and you do not specify any subpartitioning for the new partitions, the new partitions will inherit the number and tablespaces of the subpartitions in partition_name_old.

Oracle also splits corresponding local index partitions, even if they are marked UNUSABLE. The resulting local index partitions inherit all their partition-level default attributes from the local index partition being split.

If partition_name_old was not empty, Oracle marks UNUSABLE all global nonpartitioned indexes and all partitions of global indexes on the table. (This action on global indexes does not apply to index-organized tables.) In addition, if any partitions or subpartitions resulting from the split are not empty, Oracle marks as UNUSABLE all corresponding local index partitions and subpartitions.

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 partition_name_old and creates new segments for each LOB column, for each partition, even if you do not specify a new tablespace.

merge_partitions_clause

The merge_partitions_clause lets you merge the contents of two adjacent partitions of table into one new partition, and then drops the original two partitions.

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_Pnnn. If the new partition has subpartitions, Oracle assigns subpartition names of the form SYS_SUBPnnn.

If either or both of the original partitions was not empty, Oracle marks UNUSABLE all global nonpartitioned global indexes and all partitions of global indexes on the table. In addition, if the partition or any of its subpartitions resulting from the merge is not empty, Oracle marks UNUSABLE all corresponding local index partitions and subpartitions.

Restriction: You cannot specify this clause for an index-organized table or for a table partitioned using the hash method.

partition_level_subpartitioning 

The partition_level_subpartitioning clause lets you specify hash subpartitioning attributes for the new 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. 

parallel_clause 

The parallel_clause lets you parallelize the merging operation. 

exchange_partition_clause and exchange_subpartition_clause

Use the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause to exchange the data and index segments of

All of the segment attributes of the two objects (including tablespace) are also exchanged.

The default behavior is EXCLUDING INDEXES WITH VALIDATION. You must have ALTER TABLE privileges on both tables to perform this operation.

This clause facilitates high-speed data loading when used with transportable tablespaces.

See Also: Oracle8i Administrator's Guide for information on 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 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.

The logging attribute of the table and partition is also exchanged.

Restriction: Both tables involved in the exchange must have the same primary key, and no validated foreign keys can be referencing either of the tables unless the referenced table is empty.

WITH TABLE table 

Specify the table with which the partition will be exchanged. 

INCLUDING INDEXES 

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

EXCLUDING INDEXES 

Specify EXCLUDING NDEXES 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. 

WITH VALIDATION 

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. 

WITHOUT VALIDATION 

Specify WITHOUT VALIDATION if you do not want Oracle to check the proper mapping of rows in the exchanged table. 

EXCEPTIONS INTO  

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 conventional and index-organized tables.

 

 

If you create your own exceptions table, it must follow the format prescribed by one of these two scripts.  

 

Note: If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), you must create a separate exceptions table for each index-organized table to accommodate its primary-key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script.

See Also:

- The DBMS_IOT package in Oracle8i Supplied PL/SQL Packages Reference for information on the SQL scripts

- Oracle8i Performance Guide and Reference for information on eliminating migrated and chained rows

- Oracle8i Migration for compatibility issues related to the use of these scripts

 

 

Restrictions on EXCEPTIONS INTO clause:

  • This clause is not valid with subpartitions.

  • The partitioned table must have been defined with a UNIQUE constraint, and that constraint must be in DISABLE VALIDATE state.

 

 

If these conditions are not true, Oracle ignores this clause.

See Also: The constraint_clause for more information on constraint checking

 

Restrictions on exchanging partitions:

When exchanging between a hash-partitioned table and the range partition of a composite-partitioned table, the following restrictions apply:

For partitioned index-organized tables, the following additional restrictions apply:

row_movement_clause

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.

ENABLE 

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.

Restriction: You cannot specify this clause if a domain index has been built on any column of the table. 

 

Caution: Moving a row in the course of an UPDATE operation changes that row's rowid.

 

DISABLE 

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. 

parallel_clause

The parallel_clause lets you change the default degree of parallelism for queries and DML on the table.

Note:

The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior. 

NOPARALLEL 

Specify NOPARALLEL for serial execution. This is the default. 

PARALLEL 

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter. 

PARALLEL integer 

Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer. 

Restrictions:

enable_disable_clause

The enable_disable_clause lets you specify whether Oracle should apply an integrity constraint.

See Also: The enable_disable_clause of CREATE TABLE for a complete description of this clause, including notes and restrictions that relate to this statement 

TABLE LOCK

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.

Note:

Table locks are not acquired on temporary tables. 

ENABLE TABLE LOCK 

Specify ENABLE TABLE LOCK to enable table locks, thereby allowing DDL operations on the table.  

DISABLE TABLE LOCK 

Specify DISABLE TABLE LOCK to disable table locks, thereby preventing DML operations on the table. 

ALL TRIGGERS

ENABLE ALL TRIGGERS 

Specify ENABLE ALL TRIGGERS to enable all triggers associated with the table. Oracle fires the triggers whenever their triggering condition is satisfied. See CREATE TRIGGER.

To enable a single trigger, use the enable_clause of ALTER TRIGGER.

See Also: ALTER TRIGGER

 

DISABLE ALL TRIGGERS 

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. 

Examples

Nested Table Example

The following statement modifies the storage characteristics of a nested table column projects in table emp so that when queried it returns actual values instead of locators: 

ALTER TABLE emp MODIFY NESTED TABLE projects RETURN AS VALUE;

PARALLEL Example

The following statement specifies parallel processing for queries to the emp table: 

ALTER TABLE emp
   PARALLEL;

ENABLE VALIDATE Example

The following statement places in ENABLE VALIDATE state an integrity constraint named fk_deptno in the emp table: 

ALTER TABLE emp
   ENABLE VALIDATE CONSTRAINT fk_deptno
   EXCEPTIONS INTO except_table;

Each row of the emp 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 EMP table with the following statement: 

SELECT emp.*
   FROM emp e, except_table ex
   WHERE e.row_id = ex.row_id
      AND ex.table_name = 'EMP'
      AND ex.constraint = 'FK_DEPTNO';

ENABLE NOVALIDATE Example

The following statement tries to place in ENABLE NOVALIDATE state two constraints on the emp table: 

ALTER TABLE emp
   ENABLE NOVALIDATE UNIQUE (ename)
   ENABLE NOVALIDATE CONSTRAINT nn_ename;

This statement has two ENABLE clauses:

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.

Disabling a Constraint Example

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 emp table: 

ALTER TABLE emp
   ADD (CONSTRAINT check_comp CHECK (sal + comm <= 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.

Enabling Triggers Example

The following statement enables all triggers associated with the emp table: 

ALTER TABLE emp
   ENABLE ALL TRIGGERS;

DEALLOCATE UNUSED Example

The following statement frees all unused space for reuse in table emp, where the high water mark is above MINEXTENTS: 

ALTER TABLE emp
    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);

Index-Organized Table Examples

This statement modifies the INITRANS parameter for the index segment of index-organized table docindex: 

ALTER TABLE docindex INITRANS 4;

The following statement adds an overflow data segment to index-organized table docindex: 

ALTER TABLE docindex ADD OVERFLOW;

This statement modifies the INITRANS parameter for the overflow data segment of index-organized table docindex: 

ALTER TABLE docindex 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 into partitions p3_1 andp3_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.

User-Defined Object Identifier Example

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;

Add Column Example

The following statement adds a column named thriftplan of datatype NUMBER with a maximum of seven digits and two decimal places and a column named loancode of datatype CHAR with a size of one and a NOT NULL integrity constraint: 

ALTER TABLE emp 
   ADD (thriftplan NUMBER(7,2),
        loancode CHAR(1) NOT NULL);

Modify Column Examples

The following statement increases the size of the thriftplan column to nine digits: 

ALTER TABLE emp
   MODIFY (thriftplan NUMBER(9,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 emp table to 30 and 60, respectively: 

ALTER TABLE emp 
    PCTFREE 30
    PCTUSED 60;

ALLOCATE EXTENT Example

The following statement allocates an extent of 5 kilobytes for the emp table and makes it available to instance 4: 

ALTER TABLE emp
  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.

Default Column Value Examples

This statement modifies the bal column of the accounts table so that it has a default value of 0: 

ALTER TABLE accounts
  MODIFY (bal DEFAULT 0);

If you subsequently add a new row to the accounts table and do not specify a value for the bal column, the value of the bal column is automatically 0: 

INSERT INTO accounts(accno, accname)
  VALUES (accseq.nextval, 'LEWIS'); 

SELECT * 
    FROM accounts
    WHERE accname = 'LEWIS'; 

ACCNO  ACCNAME BAL 
------ ------- ---
815234  LEWIS     0

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 accounts
   MODIFY (bal 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.

Drop Constraint Examples

The following statement drops the primary key of the dept table: 

ALTER TABLE dept 
    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 dept
    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 dname column of the dept table: 

ALTER TABLE dept 
    DROP UNIQUE (dname);

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.

LOB Examples

The following statement adds CLOB column resume to the employee table and specifies LOB storage characteristics for the new column: 

ALTER TABLE employee 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 employee MODIFY LOB (resume) (CACHE);

Nested Table Examples

The following statement adds the nested table column skills to the employee table: 

ALTER TABLE employee 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_storage_clause to make the modification. You cannot query or perform DML statements on the storage table. Use the storage table only to modify the nested table column storage characteristics.

The following statement creates table vetservice with nested table column client and storage table client_tab. Nested table vetservice is modified to specify constraints: 

CREATE TYPE pet_table AS OBJECT
   (pet_name VARCHAR2(10), pet_dob DATE);

CREATE TABLE vetservice (vet_name VARCHAR2(30),
                         client   pet_table)
  NESTED TABLE client STORE AS client_tab;

ALTER TABLE client_tab ADD UNIQUE (ssn);

The following statement adds a UNIQUE constraint to nested table nested_skill_table: 

ALTER TABLE nested_skill_table ADD UNIQUE (a);

The following statement alters the storage table for a nested table of REF values to specify that the REF is scoped: 

CREATE TYPE emp_t AS OBJECT (eno number, ename char(31)); 
CREATE TYPE emps_t AS TABLE OF REF emp_t; 
CREATE TABLE emptab OF emp_t; 
CREATE TABLE dept (dno NUMBER, employees emps_t) 
   NESTED TABLE employees STORE AS deptemps; 
ALTER TABLE deptemps ADD (SCOPE FOR (column_value) IS emptab);

Similarly, to specify storing the REF with rowid: 

ALTER TABLE deptemps ADD (REF(column_value) WITH ROWID);

In order to execute these ALTER TABLE statements successfully, the storage table deptemps must be empty. Also, because the nested table is defined as a table of scalars (REFs), Oracle implicitly provides the column name COLUMN_VALUE for the storage table.

See Also:

 

REF Examples

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

Add Partition Example

The following statement adds partition jan99 to tablespace tsx: 

ALTER TABLE sales 
  ADD PARTITION jan99 VALUES LESS THAN( '970201' ) 
  TABLESPACE tsx;

Drop Partition Example

The following statement drops partition dec98: 

ALTER TABLE sales DROP PARTITION dec98;

Exchange Partition Example

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;

Modify Partition Examples

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;

Move Partition Example

The following statement moves partition depot2 to tablespace ts094: 

ALTER TABLE parts 
  MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;

Rename Partition Examples

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;

Split Partition Example

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

Truncate Partition Example

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;

Additional Examples

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 .

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index