Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Dropping Columns

Only with the release of Oracle 8i has it been possible to drop a column from a table. Prior to this it was neccessary to drop the entire table and rebuild it. Now you can mark a column as unused (logical delete) or delete it completely (physical delete).

Logical Delete

On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it.

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);

Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following.

ALTER TABLE table_name DROP UNUSED COLUMNS;

On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.

Physical Delete

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns.

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

Dropping a column from a table will cause all unused columns in that table to be dropped at the same time.

Hope this helps. Regards Tim...

Back to the Top.