This command is used to quickly remove a column from a table with reorganize all the blocks, And you could do the blocks reorganization when system is free. I noticed the problem when someone set unused a column by mistake and asking "Can I rollback the operation when no backup available?". Let's start with the following example:
SQL> DESC T_FID
Name Null? Type
----------------------- -------- -------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
SQL> ALTER TABLE T_FID SET UNUSED (COL3);
Table altered.
SQL> alter system checkpoint;
System altered.
Then you could query the COL$ directly (Columns: COL#, SEGCOL# and NAME), what's the new values for this columns? Here I will use AUL/MyDUL's DESCRIBE command to display the table structure after the operation.
AUL> desc alex.t_fid
Storage(OBJ#=7641 OBJD=7641 TS=2 FILE=3 BLOCK=1529 CLUSTER=0)
No. SEQ Column Name Type
--- --- ----------------------------- ----------------
1 1 COL1 NUMBER
2 2 COL2 NUMBER
0 3 SYS_C00004_05092817:31:11$ NUMBER
AUL> UNLOAD TABLE alex.t_fid;
2005-09-28 17:32:40
Unload OBJD=7641 FILE=3 BLOCK=1529 CLUSTER=0 ...
9,8,7
2005-09-28 17:32:40
We could see that the "SET UNUSED" operation just rename the column name, and change the column display order (COL# column) to 0 (original is 3). Although Oracle do not provide the reverse operation, but the old values still exist and can be displayed by AUL/MyDUL.But for new rows inserted after "SET UNUSED", the column value will be NULL, if there is constraint on this column, then the new rows is logically corrupted, that could be a reason why Oracle do not allow reverse operation.
Comments (2)
i m not getting clear idea and results by using AUL/MYDUL's
i m not able to rename unused column with given specification.
will u please describe it again nand more easily
Posted by jaydeep | September 18, 2006 7:28 PM
You need to edit the AULCOL.TXT file to rename the column.
In oracle, there is no way to rename the column or un-drop the column. If you want to drop it, run "alter table ... drop unused columns" command in Oracle.
Posted by anysql | September 18, 2006 9:29 PM