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