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).
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;
DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.
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...