|Oracle® Database Administrator's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
You alter a table using the
ALTER TABLE statement. To alter a table, the table must be contained in your schema, or you must have either the
ALTER object privilege for the table or the
ALTER ANY TABLE system privilege.
Many of the usages of the
ALTER TABLE statement are presented in the following sections:
Caution:Before altering a table, familiarize yourself with the consequences of doing so. The Oracle Database SQL Language Reference lists many of these consequences in the descriptions of the
If a view, materialized view, trigger, domain index, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object. See "Managing Object Dependencies" for information about how the database manages dependencies.
Modify physical characteristics (
INITRANS or storage parameters)
Move the table to a new segment or tablespace
Explicitly allocate an extent or deallocate unused space
Add, drop, or rename columns, or modify an existing column definition (datatype, length, default value,
NOT NULL integrity constraint, column expression (for virtual columns), and encryption properties.)
Modify the logging attributes of the table
Add, modify or drop integrity constraints associated with the table
Enable or disable integrity constraints or triggers associated with the table
Modify the degree of parallelism for the table
Rename a table
Put a table in read-only mode and return it to read/write mode
Add or modify index-organized table characteristics
Alter the characteristics of an external table
Add or modify
Add or modify object type, nested table, or varray columns
Many of these operations are discussed in succeeding sections.
The storage parameters
MINEXTENTS cannot be altered. All new settings for the other storage parameters (for example,
PCTINCREASE) affect only extents subsequently allocated for the table. The size of the next extent allocated is determined by the current values of
PCTINCREASE, and is not based on previous values of these parameters.
See Also:The discussions of the physical attributes clause and the storage clause in Oracle Database SQL Language Reference
ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using
ALTER TABLE. You can also use the
ALTER TABLE...MOVE statement with a
COMPRESS clause to store the new segment using table compression.
One important reason to move a table to a new tablespace (with a new datafile) is to eliminate the possibility that old versions of column data—versions left on now unused portions of the disk due to segment shrink, reorganization, or previous table moves—could be viewed by bypassing the access controls of the database (for example with an operating system utility). This is especially important with columns that you intend to modify by adding transparent data encryption.
ALTER TABLE...MOVEstatement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it, see "Redefining Tables Online".
The following statement moves the
hr.admin_emp table to a new segment, specifying new storage parameters:
ALTER TABLE hr.admin_emp MOVE STORAGE ( INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 0 );
Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked
UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.
If the table includes
LOB column(s), this statement can be used to move the table along with
LOB data and
LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the
LOB data and
LOB index segments.
See Also:"Consider Encrypting Columns That Contain Sensitive Data" for more information on transparent data encryption
Oracle Database dynamically allocates additional extents for the data segment of a table, as required. However, perhaps you want to allocate an additional extent for a table explicitly. For example, in an Oracle Real Application Clusters environment, an extent of a table can be allocated explicitly for a specific instance.
You can also explicitly deallocate unused space using the
DEALLOCATE UNUSED clause of
ALTER TABLE. This is described in "Reclaiming Wasted Space".
See Also:Oracle Real Application Clusters Administration and Deployment Guide for information about using the
ALLOCATE EXTENTclause in an Oracle Real Application Clusters environment
ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column datatype, default value, column constraint, column expression (for virtual columns) and column encryption.
You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. You can change a column from byte semantics to
CHAR semantics or vice versa. You must set the initialization parameter
BLANK_TRIMMING=TRUE to decrease the length of a non-empty
If you are modifying a table to increase the length of a column of datatype
CHAR, realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the
CHAR value in each row must be blank-padded to satisfy the new column length.
See Also:Oracle Database SQL Language Reference for additional information about modifying table columns and additional restrictions
The following statement alters the
hr.admin_emp table to add a new column named
ALTER TABLE hr.admin_emp ADD (bonus NUMBER (7,2));
If a new column is added to a table, the column is initially
NULL unless you specify the
DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table. For some types of tables (for example, tables without LOB columns), if you specify both a
NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML.
You can add a column with a
NULL constraint only if the table does not contain any rows, or you specify a default value.
See Also:Oracle Database SQL Language Reference for additional rules and restrictions for adding table columns
If you enable compression for all operations on a table, you can add columns to that table with or without default values. If you enable compression for direct-path inserts only, you can add columns only if you do not specify default values.
See Also:"Consider Using Table Compression"
Oracle Database lets you rename existing columns in a table. Use the
RENAME COLUMN clause of the
ALTER TABLE statement to rename a column. The new name must not conflict with the name of any existing column in the table. No other clauses are allowed in conjunction with the
RENAME COLUMN clause.
The following statement renames the
comm column of the
ALTER TABLE hr.admin_emp RENAME COLUMN comm TO commission;
As noted earlier, altering a table column can invalidate dependent objects. However, when you rename a column, the database updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.
Oracle Database also lets you rename column constraints. This is discussed in "Renaming Constraints".
RENAME TOclause of
ALTER TABLEappears similar in syntax to the
RENAME COLUMNclause, but is used for renaming the table itself.
You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints.
You cannot drop all columns from a table, nor can you drop columns from a table owned by
SYS. Any attempt to do so results in an error.
See Also:Oracle Database SQL Language Reference for information about additional restrictions and options for dropping columns from a table
When you issue an
ALTER TABLE...DROP COLUMN statement, the column descriptor and the data associated with the target column are removed from each row in the table. You can drop multiple columns with one statement.
The following statements are examples of dropping columns from the
hr.admin_emp table. The first statement drops only the
ALTER TABLE hr.admin_emp DROP COLUMN sal;
The next statement drops both the
ALTER TABLE hr.admin_emp DROP (bonus, commission);
If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the
ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
To mark the
mgr columns as unused, execute the following statement:
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
You can later remove columns that are marked as unused by issuing an
ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.
The data dictionary views
DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The
COUNT field shows the number of unused columns in the table.
SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT --------------------------- --------------------------- ----- HR ADMIN_EMP 2
For external tables, the
UNUSED statement is transparently converted into an
COLUMN statement. Because external tables consist of metadata only in the database, the
COLUMN statement performs equivalently to the
ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.
ALTER TABLE statement that follows, the optional clause
CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
If you enable compression for all operations on a table, you can drop table columns. If you enable compression for direct-path inserts only, you cannot drop columns.
See Also:"Consider Using Table Compression"
You can place a table in read-only mode with the
ONLY statement, and return it to read/write mode with the
WRITE statement. An example of a table for which read-only mode makes sense is a configuration table. If your application contains configuration tables that are not modified after installation and that must not be modified by users, your application installation scripts can place these tables in read-only mode.
To place a table in read-only mode, you must have the
TABLE privilege on the table or the
TABLE privilege. In addition, the
COMPATIBILE initialization parameter must be set to 11.1.0 or greater.
The following example places the
SALES table in read-only mode:
ALTER TABLE SALES READ ONLY;
The following example returns the table to read/write mode:
ALTER TABLE SALES READ WRITE;
When a table is in read-only mode, operations that attempt to modify table data are disallowed. The following operations are not permitted on a read-only table:
All DML operations on the table or any of its partitions
DATA for a type with read-only table dependents
The following operations are permitted on a read-only table:
TABLE for physical property changes
See Also:Oracle Database SQL Language Reference for more information about the