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)

【Oracle】線上重定義表

相關文章