Prior to Oracle8i, the only way to remove a column was to re-create the table
without the column name. In Oracle8i, you have the much awaited option of
dropping a column. You can drop a column that is not used immediately, or
you can mark the column as not used and drop it later.
Here is the syntax for dropping a column:
ALTER TABLE [schema.]table_name
DROP {COLUMN column_name |(column_names)}[CASCADE
CONSTRAINTS ]
DROP COLUMN drops the column name specified from the table. You can pro-vide
more than one column name separated by commas inside parentheses. The
indexes and constraints on the column are also dropped. You must specify
CASCADE CONSTRAINTS if the dropped column is part of a multicolumn con-straint;
the constraint will be dropped.
The syntax for marking a column as unused follows:
ALTER TABLE [schema.]table_name
SET UNUSED {COLUMN column_name |(column_names)}[CASCADE
CONSTRAINTS ]
You usually mark a column as unused and not dropped if the table is very
large and takes a lot of resources at peak hours. In such cases, you would
mark the column as unused and drop it later. Once the column is marked as
unused, you will not see it as part of the table definition. Let’s mark the
UPDATE_DT column in the ORDERS table as unused:
SQL>ALTER TABLE orders SET UNUSED COLUMN update_dt;
Table altered.
SQL>DESCRIBE orders
Name Null?Type
-------------------------------------------------------
ORDER_NUMBER NOT NULL NUMBER(8)
STATUS VARCHAR2(15)
ORDER_DATE DATE
QUANTITY NUMBER(10,3)
SQL>
The syntax for dropping a column already marked as unused is
ALTER TABLE [schema.]table_name
DROP {UNUSED COLUMNS |COLUMNS CONTINUE}
Use the COLUMNS CONTINUE clause to continue a DROP operation that was
previously interrupted. To clear data from the UPDATE_DT column from
the ORDERS table, do this:
ALTER TABLE orders DROP UNUSED COLUMNS;