Oracle set unused的用法

原文轉載：http://ctwen.iteye.com/blog/2018378

Oracle set unused的用法

SET UNUSED的用法


原理：清楚掉字典資訊（撤消儲存空間），不可恢復。

   可以使用 SET UNUSED 選項標記一列或者多列不可用。

   使用DROP SET UNUSED 選項刪除被被標記為不可用的列。

語法：

   ALTER TABLE table SET UNUSED (COLlist多個) 或者 ALTER TABLE table SET UNUSED COLUMN col單個;

   ALTER TABLE table DROP UNUSED COLUMNS;


set unused不會真地刪除欄位。
除了alter table drop field外，也可以
alter table set   unused field;
alter table drop unused;
set unused系統開銷比較小，速度較快，所以可以先set unuased，然後在系統負載較小時，再drop。如系統負載不大，也可以直接drop。
不管用何種方法，都不會收回空間。


如果你有這個需求，要刪除某一個表格上的某些欄位，但是由於這個表格擁有非常大量的資料，如果你在尖峰時間直接執行 ALTER TABLE ABC DROP（COLUMN）；可能會收到 ORA-01562 - failed to extend rollback segment number string，
這是因為在這個刪除欄位的過程中你可能會消耗光整個RBS，造成這樣的錯誤出現，因此這樣的做法並不是一個好方法，就算你拼命的加大RBS空間來應付這個問題，也不會是個好主意。

        我的建議做法：

        1>

        CREATE TABLE T1 （A NUMBER，B NUMBER）；

        SQL> begin 2 for i in 1 …… 100000 3 loop 4 insert into t1 values （i，100）；5 end loop；6 commit；7 end；

        SQL> select count（*） from t1；

        COUNT（*）

        100000

        2>

        SQL> ALTER TABLE T1 SET UNUSED COLUMN A CASCADE CONSTRAINTS；

        不要馬上drop column，應該先set unused讓column無法使用，避開系統尖峰時間再來處理刪除欄位裡的資料，要注意的是一但你set unused column，這個欄位是無法再回復使用的。

        3>

        重點來了，若你的欄位有一百萬筆資料，我們應該避免一次寫入那麼多的undo log，所以我準備每刪除一千筆資料就commit一次。

        SQL> alter table t1 drop unused columns checkpoint 1000；

        Table altered.

        在離峰的時間進行這樣的動作，應該可以避免 ORA-01562 的錯誤發生。

剛才有個人問我如何修復被設定為UNUSED的欄位，我考慮了一下，以下的方法可以恢復（以下步驟執行前要做好備份），沒有經驗的DBA不要輕易嘗試。

1、建立實驗表TTTA

SQL> CREATE TABLE TTTA ( A INTEGER,B INTEGER,C VARCHAR2(10),D INTEGER);

表已建立。

SQL> INSERT INTO TTTA VALUES (1,2,'3',4);

已建立 1行。

SQL> INSERT INTO TTTA VALUES (2,3,'4',5);

已建立 1行。

SQL> COMMIT;

提交完成。
ALTER TABLE TTTA SET UNUSED COLUMN C;

2、以下進行恢復

SQL> SELECT OBJ# FROM OBJ$ WHERE NAME='TTTA';

      OBJ#
----------
     32067

SELECT COL#,INTCOL#,NAME FROM COL$ WHERE OBJ#=32067;
      COL#    INTCOL# NAME
---------- ---------- ------------------------------
         1          1 A
         2          2 B
         0          3 SYS_C00003_08031720:09:55$   被UNUSED的欄位
         3          4 D

SQL> SELECT COLS FROM TAB$ WHERE OBJ#=32067;

      COLS
----------
         3      ------欄位數變為3了

 

SQL> UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=32067;

已更新4行。

SQL> UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=32067;

已更新 1行。

UPDATE COL$ SET NAME='C' WHERE OBJ#=32067 AND COL#=3;

UPDATE COL$ SET PROPERTY=0 WHERE OBJ#=32067;
SQL> COMMIT;

3、重啟資料庫
SQL> SELECT * FROM SCOTT.TTTA;

         A          B C                   D
---------- ---------- ---------- ----------
         1          2 3                   4
         2          3 4                   5

恢復完成