Oracle Table SET UNUSED Column

We can set a column in a table as UNUSED. When we specify this clause the column becomes invisible and inaccessible. It is equivalent to dropped column. This column can be dropped at a later time when there is less resource consumption.We’ve seen that it is not possible to use SET UNUSED clause with the SYS tables
Now we demonstrate the usage of SET UNUSED as a normal user(schema user) :
SQL> connect temp/temp
Connected.
SQL> desc test;
Name                                      Null?    Type
—————————————– ——– ————————
ID                                        NOT NULL NUMBER(38)
NAME                                               VARCHAR2(10)

SQL> insert into test values(1,’info’);
1 row created.
SQL> insert into test values(2,’new’);
1 row created.
SQL> alter table test set unused (id);
Table altered.
SQL> desc test;
Name                                      Null?    Type
—————————————– ——– ————————–
NAME                                               VARCHAR2(10)
DROP UNUSED Column Oracle Table:
If a column in a database table is set as UNUSED it is essential to drop it at a later point of time. This can be done as a bulk UNUSED column drop. It is simple to specify DROP UNUSED COLUMNS clause along with ALTER TABLE. Here is how we go about it :
SQL> alter table test drop unused columns;
Table altered.

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner