7
SQL Statements (continued)

<

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	is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.
table	is 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 columns of nested-table or varray type. You can add columns of other types. Specify referential integrity (foreign key) constraints for an added or modified column
	Specify the following clauses of the LOB_storage_clause for an added or modified LOB column: TABLESPACE, storage_clause, LOGGING|NOLOGGING, or the LOB_index_clause. Specify the physical_attribute_clause, nested_table_storage_clause, parallel_clause, allocate_extent_clause, deallocate_unused_clause, or any of the index-organized table clauses
	Exchange partitions between a partition and a temporary table Specify LOGGING or NOLOGGING Specify MOVE
	Note: If you alter a table that is a master table for one or more materialized views, the materialized views are marked INVALID. Invalid materialized views cannot be used by query rewrite and cannot be refreshed. To revalidate a materialized view, see "ALTER MATERIALIZED VIEW / SNAPSHOT". For more information on materialized views in general, see Oracle8i Tuning.
ADD add_column_options	adds a column or integrity constraint. If you add a column, the initial value of each row for the new column is null. For a description of the keywords and parameters of this clause, see "CREATE 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 "CREATE VIEW".
	Restrictions: You cannot add a LOB column to a partitioned index-organized table. (This restriction does not apply to nonpartitioned index-organized tables.) You cannot add a column with a NOT NULL constraint if table has any rows. If you specify this clause for an index-organized table, you cannot specify any other clauses in the same statement.
	table_ref_constraint column_ref_constraint	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. For syntax and description of these constraints, including restrictions, see the "constraint_clause".
	column_constraint	adds or removes 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. See the "constraint_clause".
	table_constraint	adds or modifies an integrity constraint on the table. See the "constraint_clause".
LOB_storage_clause	specifies 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.
	lob_item	is 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	specifies the name of the LOB data segment. You cannot use lob_segname if more than one lob_item is specified.
	ENABLE | DISABLE STORAGE IN ROW	specifies whether the LOB value is stored in the row (inline) or outside of the row. (The LOB locator is always stored in the row 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 outside of the row regardless of the length of the LOB value. Restriction: You cannot change STORAGE IN ROW once it is set. Therefore, you can specify this clause only as part of the add_column_options clause, not as part of the modify_column_options clause.
	CHUNK integer	specifies 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. You cannot change the value of CHUNK once it is set.
		Note: The value of 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.
	PCTVERSION integer	is the maximum percentage of overall LOB storage space 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.
	LOB_index_clause	This clause is deprecated as of Oracle8i. Oracle generates an index for each LOB column. The LOB indexes are system named and system managed, and reside in the same tablespace as the LOB data segments. Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. For information on how Oracle manages LOB indexes in tables migrated from earlier versions, see Oracle8i Migration.
partition_LOB_storage_clause	lets you specify a separate LOB_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 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_LOB_storage_clauses per ALTER TABLE statement, and all LOB_storage_clauses must precede the list of partition_LOB_storage_clauses.
MODIFY modify_column_options	modifies 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. You can change a 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. You can change any column's datatype or decrease any column's size if all rows for the column contain nulls. 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.
	Restrictions: You cannot modify the datatype or length of a column that is part of a table or index partitioning or subpartitioning key. You cannot modify the definition of a column on which a domain index has been built. If you specify this clause for an index-organized table, you cannot specify any other clauses in the same statement.
	column	is the name of the column to be added or modified. 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.
	datatype	specifies a new datatype for an existing column. 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. If you change the datatype of a column in a materialized view container table, the corresponding materialized view is invalidated. To revalidate a materialized view, see "ALTER MATERIALIZED VIEW / SNAPSHOT".
		Restrictions: You cannot specify a column of datatype ROWID for an index-organized table, but you can specify a column of type UROWID. You cannot change a column's datatype to LOB or REF.
	DEFAULT	specifies 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. A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
MODIFY CONSTRAINT constraint	modifies the state of an existing constraint named constraint. For a description of all the keywords and parameters of constraint_state, see the "constraint_clause".
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: If you alter the values of PCTTHRESHOLD or the INCLUDING column as part of this ALTER TABLE statement, the overflow data segment is rebuilt. If any of out-of-line columns (LOBs, varrays, nested table columns) in the index-organized table are moved explicitly, then the overflow data segment is also 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.
	ONLINE	specifies that DML operations on the index-organized table are 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	enables and disables 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	specifies the tablespace into which the rebuilt index-organized table is stored.
	Restrictions: If you specify MOVE, it must be the first clause. For an index-organized table, the only clauses outside this clause that are allowed are the physical_attribute_clause and the parallel_clause. For heap-organized tables, you can specify those two clauses and the LOB_storage_clauses. You cannot MOVE an entire partitioned table (either heap or index organized). You must move individual partitions or subpartitions. See "move_partition_clause" and "move_subpartition_clause".
	Notes regarding LOBs: For any LOB columns you specify in this clause: Oracle drops the old LOB data segment and corresponding index segment and creates new segments, even if you do not specify a new tablespace. If the LOB index in table resided in a different tablespace from the LOB data, Oracle collocates the LOB index with the LOB data in the LOB data's tablespace after the move.
physical_attributes_clause	changes the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and storage characteristics. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of "CREATE TABLE" and the "storage_clause". Restriction: You cannot specify the PCTUSED parameter for the index segment of an index-organized table.
	WARNING: 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	changes what is returned when a collection item is retrieved from the database.
	collection_item	is the name of a column-qualified attribute whose type is nested table or varray.
	RETURN AS	specifies what Oracle returns 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.
storage_clauses:
modify_LOB_storage_clause	modifies the physical attributes of the LOB lob_item. You can specify only one lob_item for each modify_LOB_storage_clause. Restriction: You cannot modify the value of the INITIAL parameter in the storage_clause when modifying the LOB storage attributes.
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.
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.
nested_table_storage_clause	enables you to 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: You cannot specify the parallel_clause. You cannot specify TABLESPACE (as part of the segment_attributes_clause) for a nested table. The tablespace is always that of the parent table.
	nested_item	is the name of a column (or a top-level attribute of the table's object type) whose type is a nested table.
	storage_table	is 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.
drop_constraint_clause	drops 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	drops the table's PRIMARY KEY constraint.
	UNIQUE	drops the UNIQUE constraint on the specified columns.
	CONSTRAINT	drops the integrity constraint named constraint.
	CASCADE	drops all other integrity constraints that depend on the dropped integrity constraint.
	Restrictions: You cannot drop a UNIQUE or PRIMARY KEY constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the CASCADE clause. If you omit CASCADE, Oracle does not drop the PRIMARY KEY or UNIQUE constraint if any foreign key references it. You cannot drop a primary key constraint (even with the CASCADE clause) on a table that uses the primary key as its object identifier (OID). If you drop a referential integrity constraint on a REF column, the REF column remains scoped to the referenced table. You cannot drop the scope of the column.
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.
	SET UNUSED	marks 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 as unused in the data dictionary views USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, and ALL_UNUSED_COL_TABS. For information on these views, see Oracle8i Reference.
		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 as 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.
		Note: Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns per table. (For more information, see "CREATE TABLE".) Also, if you mark a column of datatype LONG as UNUSED, you cannot add another LONG column to the table until you actually drop the unused LONG column.
	DROP	removes 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 as unused in the target table are dropped at the same time.
		When the column data is dropped: All indexes defined on any of the target columns are also dropped. All constraints that reference a target column are removed. If any statistics types are associated with the target columns, Oracle disassociates the statistics from the column with the FORCE option and drops any statistics collected using the statistics type. For more information on disassociating statistics types, see "DISASSOCIATE STATISTICS".
		Note: If a constraint also references a nontarget column, Oracle returns an error and does not drop the column unless you have specified the CASCADE CONSTRAINTS clause. If you have specified that clause, Oracle removes all constraints that reference any of the target columns.
	DROP UNUSED COLUMNS	removes 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.
	column	specifies 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.
	CASCADE CONSTRAINTS	drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns, and drops 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.
	INVALIDATE	Note: Currently, Oracle executes this clause regardless of whether you specify the keyword INVALIDATE.
		Oracle 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. For more information on dependencies, refer to Oracle8i Concepts. 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.
	CHECKPOINT	specifies that a checkpoint for the drop column operation will be applied 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.
	DROP COLUMNS CONTINUE	continues 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: Each of the parts of this clause can be specified only once in the statement and cannot be mixed with any other 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));
	You can drop an object type column only as an entity. Dropping an attribute from an object type column is not allowed. If you drop a nested table column, its storage table is removed. If you drop a LOB column, the LOB data and its corresponding LOB index segment are removed.
	If you drop a BFILE column, only the locators stored in that column are removed, not the files referenced by the locators. You can drop a column from an index-organized table only if it is not a primary key column. The primary key constraint of an index-organized table can never be dropped, so you cannot drop a primary key column even if you have specified CASCADE CONSTRAINTS.
	You can export tables with dropped or unused columns. However, you can import a table only if all the columns specified in the export files are present in the table (that is, none of those columns has been dropped or marked unused). Otherwise, Oracle returns an error. You cannot drop a column on which a domain index has been built.
	You cannot use this clause to drop: A pseudocolumn, clustered column, or partitioning column. (You can drop nonpartitioning columns from a partitioned table if all the tablespaces where the partitions were created are online and in read-write mode.) A column from a nested table, an object table, or a table owned by SYS
allocate_extent_clause	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 composite-partitioned table.
	SIZE	specifies 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	specifies 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	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. For more information, see Oracle8i Concepts. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
	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.
deallocate_unused_clause	explicitly deallocates 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 (as described in "storage_clause").
	KEEP	specifies the number of bytes above the high water mark that the table, overflow data segment, LOB data segment, or LOB index will 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	for data that is accessed frequently, specifies 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. Restriction: You cannot specify CACHE for index-organized tables.
NOCACHE	for data that is not accessed frequently, specifies 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. For LOBs, 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.)
	Restriction: You cannot specify NOCACHE for index-organized tables.
MONITORING	specifies that Oracle can 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. For more information on using this clause, see Oracle8i Tuning.
NOMONITORING	specifies that Oracle will not collect modification statistics on table. Restriction: You cannot specify MONITORING or NOMONITORING for a temporary table.
LOGGING| NOLOGGING	specifies 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.
	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.
	For more information about the logging_clause and parallel DML, see Oracle8i Parallel Server Concepts and Administration.
RENAME TO	renames table to new_table_name.
	Note: Using this clause will invalidate any dependent materialized views. For more information on materialized views, see "CREATE MATERIALIZED VIEW / SNAPSHOT" and Oracle8i Tuning.
records_per_block_clause	determines 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: You cannot specify either MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index. You cannot specify this clause for an index-organized table or nested table.
	MINIMIZE	instructs 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.
	NOMINIMIZE	disables the MINIMIZE feature. This is the default.
alter_overflow_clause	modifies 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	specifies the percentage of space reserved in the index block for an index-organized table row. Any portion of the row that exceeds the specified threshold is stored in the overflow area. PCTTHRESHOLD must be a value from 1 to 50. Restrictions: You cannot reduce the value of PCTTHRESHOLD so much that the primary key will not fit. You cannot specify PCTTHRESHOLD for individual partitions of an index-organized table.
	INCLUDING column_name	specifies the column at which to divide an index-organized table row into index and overflow portions. All non-primary-key columns that follow column_name are stored in the overflow data segment. The column_name is either the name of the last primary key column or any subsequent non-primary-key column. If you use the drop_column_clause to drop (or mark unused) a column defined as an INCLUDING column, the column stored immediately before this column will become the new INCLUDING column.
	overflow_clause	specifies 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. For more information, see "CREATE TABLE". Restriction: You cannot specify OVERFLOW for a partition of a partitioned index-organized table unless the table already has an overflow segment.
	add_overflow_clause	adds 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	specifies 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.
	Restrictions: The PCTTHRESHOLD, COMPRESS, physical_attributes_clause, and overflow_clause are valid only for partitioned index-organized tables. You cannot specify the PCTUSED parameter for the index segment of an index-organized table. You can specify COMPRESS only if compression is already specified at the table level.
modify_partition_clause	modifies 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: If you specify the allocate_extent_clause, Oracle will allocate an extent for each subpartition of partition. If you specify deallocate_unused_clause, Oracle will deallocate unused storage from each subpartition of partition. Any other attributes changed in this clause will be changed in subpartitions of partition as well, overriding existing values. To avoid changing the attributes of existing subpartitions, use the FOR PARTITION clause of the modify_default_attributes_clause.
	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	adds 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_SUBPnnnn If you do not specify TABLESPACE, the new subpartition will reside in the default tablespace of partition.
	COALESCE SUBPARTITION	specifies that Oracle should 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 clause	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	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.
	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.
rename_partition/ subpartition_clause	renames 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	moves 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. For more information on logical rowids, see Oracle8i Concepts.
	Restrictions: If partition is a hash partition, the only attribute you can specify in this clause is TABLESPACE. You cannot move a partition of a composite-partitioned table. You must move each subpartition separately with the move_subpartition_clause. You cannot specify this clause for a partition containing subpartitions. However, you can move subpartitions using the move_subpartition_clause.
move_subpartition_clause	moves 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	adds 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. For a discussion of factors that might impose practical limits less than this number, refer to Oracle8i Administrator's Guide.
	Restrictions: If the first element of the partition bound of the high partition is MAXVALUE, you cannot add a partition to the table. Instead, use the split_partition_clause to add a partition at the beginning or the middle of the table. The compression_clause, physical_attributes_clause, and OVERFLOW are valid only for a partitioned index-organized table. You cannot specify the PCTUSED parameter for the index segment of an 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.
	VALUES LESS THAN (value_list)	specifies 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.
	partition_level_subpartitioning	is permitted only for a composite-partitioned table. This clause lets you specify particular hash subpartitions for partition. You specify composite partitioning in one of two ways:
		You can specify individual subpartitions by name, and optionally the tablespace where each should be stored, or You can specify the number of subpartitions (and optionally one or more tablespaces where they are to be stored). In this case, Oracle assigns partition names of the form SYS_SUBPnnn. 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 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, new_partition_name will inherit the table-level default subpartitioning (see "CREATE TABLE").
add_hash_partition_clause	adds 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. For more information on hash partitioning, see "CREATE TABLE" and Oracle8i Concepts.
	parallel_clause	lets you specify whether to parallelize the creation of the new partition.
coalesce_partition_clause	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	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 the merge_partitions_clause of this statement.
	If the table has LOB columns, the LOB data and LOB index partitions (and their subpartitions, if any) corresponding to partition are also dropped. Oracle drops local index partitions and subpartitions corresponding to partition, even if they are marked UNUSABLE. Oracle marks UNUSABLE all global nonpartitioned indexes defined on the table and all partitions of global partitioned indexes, unless the partition being dropped or all of its subpartitions are empty. 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.
	Restriction: If table contains only one partition, you cannot drop the partition. You must drop the table.
truncate_partition_clause truncate_subpartition_clause	PARTITION removes all rows from partition or, if the table is composite-partitioned, all rows from partition's subpartitions. 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	deallocates space from the deleted rows and makes it available for use by other schema objects in the tablespace.
	REUSE STORAGE	keeps 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	from an original partition partition_name_old, creates 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)	specifies 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	describes the two partitions resulting from the split.
	partition_description, partition_description	specifies 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.