The DROP COLUMN statement is a new feature in Oracle 8i that allows a 
column to be dropped from a table.  Columns can either be dropped 
immediately, or flagged as UNUSED and physically dropped later.  Once 
flagged as UNUSED, a column is not accessible nor visible in the data 
dictionary views or seen in the output of DESCRIBE.  Once a column is 
dropped or the UNUSED flag is set, it isn't possible to rollback the 
operation.  If a column is dropped, then all other columns in the table 
marked as 'UNUSED' will also be dropped at the same time.

Note that the COMPATIBLE parameter must be set to 8.1.0 or greater in 
order for the DROP COLUMN statement to be valid.


SQL> create table test(a varchar(2), b varchar(2), c varchar(2));

Table created.
 
SQL> insert into test values ('A1','B1','C1');

1 row created.

SQL> insert into test values ('A2','B2','C2');

1 row created.

SQL> insert into test values ('A3','B3','C3');

1 row created.

SQL> insert into test values ('A4','B4','C4');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

A  B  C
-- -- --
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
 
SQL> -- Mark column B as UNUSED
SQL> alter table test set unused column b;

Table altered.
 
SQL> -- Show tables with unused columns
SQL> select * from sys.dba_unused_col_tabs;

OWNER                       TABLE_NAME       COUNT
------------------          --------------   ---------
SYSTEM                      TEST             1
 
SQL> -- Drop all columns marked as unused
SQL> alter table test drop unused columns;

Table altered.
 
SQL> -- Drop column C
SQL> alter table test drop column c cascade constraints;

Table altered.
 
SQL> select * from test;

A
--
A1
A2
A3
A4