alter table table_name set unused
There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused.
MARKING UNUSED COLUMN
sql>
TOTAL_ROWS NUMBERdesc abc_testName Null Type---------- ---- ------------NAME VARCHAR2(20)
sql>
alter table abc_test add (lname varchar2(20))table ABC_TEST altered.
sql>
desc abc_testName Null Type---------- ---- ------------NAME VARCHAR2(20)TOTAL_ROWS NUMBERLNAME VARCHAR2(20)
sql>
alter table abc_test set unused (lname)
table ABC_TEST altered.
sql>
desc abc_test
TOTAL_ROWS NUMBERName Null Type---------- ---- ------------NAME VARCHAR2(20)
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.
Note :
Dropping the unused column will still put a lock on the base table. I suggest you to drop
unused column during maintenance period, to avoid locking.
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.Physically dropping column
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; -- 1 column
ALTER TABLE table_name DROP (column_name1, column_name2); -- multiple columns
