Oracle 8i adds a new feature to
drop and set unused columns in a table. First feature
allows you to mark a column as unused and the second
one lets you drop the unused column from the table
to create more free space. Lets look at them in
detail.
Marking Columns in a Table as
Unused
Columns in a table can be
marked as unused. Unused columns are treated as
if they were dropped, even though their column data
remains in the table's rows. This feature is useful
when you just dont want to drop the column during
peak periods and want to remove the column from
regular access.
Alter table dbatest set unused
( c3 );
Alter table dbatest set unused (c1, c4);
Until you actually
drop the columns, the columns are counted towards
the Overall Column Limit (1000) in Oracle 8.1 and
also if you have a long column set to unused, you
cannot add another long column until you are have
dropped the old one. The dictionary views dba_unused_col_tabs
and user_unused_col_tabs can be used to view the
columns that are currently marked as unused.
Dropping a column or UnUsed Column
from a Table
This feature drops the column
from a table and releases any space back to the
segment. When you use the drop column clause, it
will also drop any columns that were previously
marked as being unused. This clause also causes
any indexes, constraints and statistics on this
column to be dropped.
Alter table dbatest drop column ( c9 );
Alter table dbatest drop unused columns; We just
wanted to go one step ahead and drop all columns
and Oracle reports a ORA-12983-Cannot drop all columns
in a table.
Restrictions
You cannot combine drop
an set unused clauses in the same statement. Also
you cannot drop a column from an objecty type table
or a nested table or a partitioning key column or
a parent key column. Another good restriction is
that you cannot drop a column from any of the tables
owned by sys.
