Solution #583 - Dropping columns in Oracle

Exploring better ways to drop columns in Oracle, especially when dropping columns from large tables.

The basic syntax for dropping columns:
ALTER TABLE table_name DROP COLUMN column_name;

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);

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.