Oracle中關於恢複unused列的方法

設定unused的作用是為了在cpu、記憶體等資源不充足的時候，先做上unused標記再等資料庫資源閒置時候用drop set unused刪除

設定unused列之後，並不是將該列資料立即刪除，而是被隱藏起來，物理上還是存在的，以下為恢複步驟：對資料字典不熟悉的朋友測試前做好備份工作

SQL> conn scott/Oracle
Connected.
SQL> create table xs (id number, name char(10),age number);
Table created.
SQL> insert into xs values(1,'JACK',20);
1 row created.
SQL> insert into xs values(2,'BILL',21);
1 row created.
SQL> insert into xs values(3,'TOM',22);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xs;
>select * from xs;
 ID      NAME              AGE
---------- ----------        ----------
        1 JACK              20
        2 BILL              21
        3 TOM                22
SQL> alter table xs set unused column AGE;
Table altered.
SQL> select * from xs;
 ID      NAME
---------- ----------
        1 JACK
        2 BILL
        3 TOM
SQL> SET LINESIZE 200 設定顯示列寬
SQL> SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;
OBJECT_ID OBJECT_NAME
--------------------------------------------------------------------------------------------------    51147 PK_DEPT
    51146 DEPT
    51148 EMP
    51149 PK_EMP
    51150 BONUS
    51151 SALGRADE
    52613 D
    52614 D1
    52615 D3
    52696 BIG
    52717 XS------------------------ OBJECT_ID=52717
11 rows selected.
SQL> conn / as sysdba
Connected.
SQL> select col#,intcol#,name from col$ where obj#=52717;
      COL#    INTCOL# NAME
---------- ---------- ------------------------------
        1          1 ID
        2          2 NAME
        0          3 SYS_C00003_12092313:06:51$----------原來的列名為C，被系統修了.
SQL> select cols from tab$ where obj#=71930;
COLS
----------
      2    -----------------------系統的欄位數目也發生了變化
SQL> update col$ set col#=intcol# where obj#=52717;
3 rows updated.
SQL> update tab$ set cols=cols+1 where obj#=52717;
1 row updated.
SQL> update col$ set name='AGE' where obj#=52717 and col#=3;
1 row updated.
SQL> update col$ set property=0 where obj#=52717;
3 rows updated.
SQL> commit;
Commit complete.
SQL> startup force;  -----------這一步是必不可少的
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select * from scott.xs;
ID    NAME      AGE
---------- ---------- ----------
        1 JACK      20
        2 BILL        21
        3 TOM        22