【Oracle】線上重定義表

楊奇龍發表於2012-01-01
Oracle
Oracle從9i版本開始提供了線上重定義表功能,透過呼叫DBMS_REDEFINITION包,可以在修改表結構的同時允許DML操作。
線上重定義表具有以下功能：
1 修改表的儲存引數
2 可以將錶轉移到其他表空間
3 增加並行查詢選項
4 增加或刪除分割槽
5 重建表以減少碎片
6 將堆表改為索引組織表或相反的操作
7 增加或刪除一個列
呼叫DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色和以下許可權：
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

1.線上重定義的方法
方法一:基於主鍵。要求原表和重定義後的表有相同的主鍵列。這是預設方法！
方法二:基於ROWID。該方法不能用於索引組織表(IOT)，並且在重定義的表中會新增隱藏列(M_ROW$$),建議將該列標記為unused或刪除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
2 使用dbms_redefinition.can_redef_table()，檢查原表是否支援進行線上重定義。
如果表中沒有主鍵，則檢查失敗！
YANG@yangdb> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T1', DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T1', DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "YANG"."T1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1

YANG@yangdb> alter table t1 add primary key(id); 
Table altered.
YANG@yangdb> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T1', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
建立測試表rbol
YANG@yangdb> create table rbol (id number primary key,time date);
Table created.
YANG@yangdb> insert into rbol select rownum ,created from dba_objects;
73987 rows created.
YANG@yangdb> commit;
Commit complete.
3 檢查是否可以進行線上重定義！
YANG@yangdb> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'RBOL', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
4 根據重定義後你期望得到的結構建立中間表。比如：採用分割槽表,刪除或者增加欄位等，建立中間表！
20:22:14 yang(45)@yangdb> create table rbol_tmp (id number primary key,time date,val varchar2(10));
Table created.
20:22:36 yang(45)@yangdb> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'RBOL', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
20:22:53 yang(45)@yangdb> exec dbms_redefinition.start_redef_table(user,'RBOL','RBOL_TMP');
PL/SQL procedure successfully completed.
5 在重定義過程中對原表rbol進行dml操作
20:24:39 yang(32)@yangdb> delete from rbol where rownum < 10;
9 rows deleted.
20:24:44 yang(32)@yangdb> select count(1) from rbol;
  COUNT(1)
----------
     73479
1 row selected.
Elapsed: 00:00:00.00
20:24:49 yang(32)@yangdb> select count(1) from rbol_tmp;
  COUNT(1)
----------
     73488
1 row selected.
20:25:43 yang(32)@yangdb> commit;
Commit complete.
6 如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。
20:25:55 yang(32)@yangdb> exec dbms_redefinition.sync_interim_table(user,'RBOL','RBOL_TMP');
PL/SQL procedure successfully completed.
20:26:06 yang(32)@yangdb> select count(1) from rbol_tmp;
  COUNT(1)
----------
     73479
1 row selected.
20:26:13 yang(32)@yangdb> select count(1) from rbol;    
  COUNT(1)
----------
     73479
1 row selected.
7 結束重定義步驟！
20:25:25 yang(45)@yangdb> exec dbms_redefinition.finish_redef_table(user,'RBOL','RBOL_TMP');
PL/SQL procedure successfully completed.
20:26:43 yang(32)@yangdb> desc rbol
 Name   Null?   Type
 ------ ------- --------
 ID    NOT NULL NUMBER
 TIME           DATE
 VAL            VARCHAR2(10)

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

相關文章