ALTER TABLE
ALTER TABLE [schema
.]table_name
{ [ADD (column
datatype
[DEFAULTexpression
] [column_constraint_clause
] )] [MODIFY (column
[datatype
] [DEFAULTexpression
] [column_constraint_clause
] }] [PCTFREEinteger
] [PCTUSEDinteger
] [INITRANSinteger
] [MAXTRANSinteger
] [LOGGING | NOLOGGING] [STORAGE ( [NEXTinteger
[K | M] ] [MAXEXTENTS {integer
| UNLIMITED} ] [PCTINCREASEinteger
] [FREELISTSinteger
] [FREELIST GROUPSinteger
] ] ) ] [DROP { PRIMARY KEY [CASCADE] | UNIQUE (column
[,column
...] [CASCADE] | CONSTRAINTconstraint_name
[CASCADE]} ] [ALLOCATE EXTENT ([DATAFILE 'filename
'] [SIZEinteger
[K | M] ]) ] [DEALLOCATE UNUSED [KEEPinteger
[K | M]] [CACHE | NOCACHE] [RENAME TOtable_name
] [PARALLEL ( {DEGREE {integer
| DEFAULT} | INSTANCES {integer
| DEFAULT} } ) ] [NOPARALLEL] [DROP PARTITIONpartition
] [TRUNCATE PARTITIONpartition
] [MODIFY PARTITIONpartition
[REBUILD] UNUSABLE LOCAL INDEXES] [ADD PARTITIONpartition
VALUES LESS THAN (valuelist
) [RENAME PARTITIONpartition
TOnewpartition
] [EXCHANGE PARTITIONpartition
WITH TABLEtable_name
[ {INCLUDING | EXCLUDING} INDEXES ] [ {WITH | WITHOUT} VALIDATION ] ] } [ENABLE { ALL TRIGGERS | UNIQUE (column
[,column
...] [CASCADE] | PRIMARY KEY [CASCADE] | CONSTRAINTconstraint_name
| }] [DISABLE { ALL TRIGGERS | UNIQUE (column
[,column
...] [CASCADE] | PRIMARY KEY [CASCADE] | CONSTRAINTconstraint_name
}] [ENABLE TABLE LOCK] [DISABLE TABLE LOCK]
Modifies the column characteristics of a table (table_name), the storage characteristics of the table, or integrity constraints associated with the table or its columns.
Adds a column or integrity constraint to the table with the specified datatype.
Specifies a default value for the column. The specified expression must match the datatype of the column.
Adds or removes a column constraint, using the syntax shown in the later column_constraint_clause section.
Changes the definition of an existing column in the table; you can optionally change the datatype of the column, provide a default expression, and specify column constraints.
Specifies the percentage of space to be reserved in each data block for future updates to rows contained in that block. The value may be in the range - 99, and the default value is 10.
Specifies the minimum percentage of space that will be maintained as used in each data block. The value may be in the range 1- 99; the default value is 40.
Specifies the initial number of transaction entries allocated to each block.
Specifies the maximum number of transaction entries allocated to each block.
Specifies that future direct path DML operations are to be logged to the redo logs.
Specifies that future direct path DML operations are not to be logged to the redo logs.
Specifies the physical characteristics of the table as follows:
Specifies the size of the next extent in bytes, kilobytes, or megabytes. If the value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.
Specifies the maximum number of extents that may be allocated for this table. The default will vary according to the database blocksize. Specify UNLIMITED for unlimited expansion.
Specifies the percentage by which each extent will grow over the previous extent. The default is 50, which means that each extent will be one-and-one-half times larger than the previous extent.
Specifies the number of free lists contained in each freelist group in this table. The default is 1 and the maximum depends on the database blocksize.
Specifies the number of groups of free lists for this table. The default is 1. This parameter should be used only with the Parallel Server option running in parallel mode.
Specifies that the table’s primary key is to be dropped.
Specifies that the UNIQUE constraint on the specified columns is to be dropped.
Specifies that the named integrity constraint is to be dropped.
Explicitly allocates a new extent for the table; you can optionally specify a datafile and size.
Releases storage above the highwater mark.
Specifies that blocks retrieved from this table during full table scans are placed at the beginning of the least-recently-used (LRU) list. This allows small lookup tables to remain in the SGA.
Specifies that blocks retrieved from this table during full table scans are placed at the end of the LRU list. This is the default behavior.
Renames table to table_name.
Specifies the level of parallelism to be supported, based on the following parameters:
Specifies the degree of parallelism. An integer value specifies how many slave processes can be used. Specify DEFAULT to use the default value for the tablespace.
Specifies the number of instances that can be used to execute slave processes. Specify DEFAULT to use the default value specified for the tablespace.
Specifies that no parallel operations are to be performed.
Drops the specified partition, and all data contained in that partition, from the table.
Truncates the specified partition.
Changes the physical attributes of the specified partition.
Adds a new partition at the high end of the partitions. Cannot be used if the existing top partition has the key word MAXVALUE.
Renames the specified partition TO newpartition.
Specifies that the partition and table (specified in WITH TABLEtable_name) are to be exchanged. The segments are kept intact, and only the data dictionary is modified.
Specifies that local indexes on the partition and indexes on the table are to be exchanged as well.
Specifies that Oracle is to validate that all rows in the table are capable of being included in the partition.
Specifies that the exchange is to occur without Oracle’s verifying that all rows are legitimate.
Enables a single integrity constraint or all triggers associated with the table (see the definitions for the keywords under DROP). ALL TRIGGERS enables all triggers.
Disables a single integrity constraint or all triggers associated with the table (see the definitions for the keywords under DROP). ALL TRIGGERS disables all triggers.
Enables DML and DDL locks on a table in a Parallel Server environment.
Disables DML and DDL locks on a table in a Parallel Server environment.