Oracle set unused的用法.sql

Nalternative發表於2011-09-14
--http://space.itpub.net/8111049/viewspace-666550
原理:清楚掉字典資訊(撤消儲存空間),不可恢復。
  可以使用 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空間來應付這個問題,也不會是個好主意。
/

        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不要輕易嘗試。(最好別做)
 CREATE TABLE T1(A NUMBER,B NUMBER)
/
begin
  for i in 1 .. 100000
  loop
    insert into t1 values (i,100);
  end loop;
  commit;
end;
/
select count(*) from t1;
/
ALTER TABLE T1 SET UNUSED COLUMN A CASCADE CONSTRAINTS
/
alter table t1 drop unused columns checkpoint 1000
/
select * from t1
/
CREATE TABLE TTTA ( A INTEGER,B INTEGER,C VARCHAR2(10),D INTEGER);
/
INSERT INTO TTTA VALUES (1,2,'3',4);
INSERT INTO TTTA VALUES (2,3,'4',5);
/
ALTER TABLE TTTA SET UNUSED COLUMN C;
/
--sys使用者
SELECT OBJ# FROM OBJ$ WHERE NAME='TTTA';
75533
SELECT COL#,INTCOL#,NAME FROM COL$ WHERE OBJ#=75533;
COL# INTCOL# NAME
1 1 A
2 2 B
3 3 C
4 4 D

 SELECT COLS FROM TAB$ WHERE OBJ#=75533;
4

UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=75533;
UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=75533;

UPDATE COL$ SET NAME='C' WHERE OBJ#=75533 AND COL#=4;
UPDATE COL$ SET PROPERTY=0 WHERE OBJ#=75533;
--重啟資料庫
SELECT * FROM TTTA;
 
 
 
 
 
 
 
 
 
 
 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25323853/viewspace-707392/,如需轉載,請註明出處,否則將追究法律責任。

相關文章