Dropping Columns

Oracle 8i introduced the ability to drop a column from a table. Prior to this it was necessary 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.

(No)Compress Tabellen

Komprimierte Tabellen sind vor dem physikalischen Löschen auf NOCOMPRESS zu setzen, ggfs. partitionsweise:

ALTER TABLE table_name MOVE PARTITION P_1 NOCOMPRESS 

ALTER TABLE table_name MOVE PARTITION P_1 COMPRESS

Quelle: https://oracle-base.com/articles/8i/dropping-columns