Dropping large columns in database - ORACLE

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> 

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 

sql>  

alter table abc_test add (lname varchar2(20))




table ABC_TEST altered.

sql>  

desc abc_test




Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER       

LNAME           VARCHAR2(20) 

sql> 

alter table abc_test set unused (lname)

table ABC_TEST altered.

sql>  

desc abc_test

Name       Null Type         

---------- ---- ------------ 

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 




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