Home » Articles » 8i » Here

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
• 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 by:

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