【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.

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);

4 根據重定義後你期望得到的結建構立中間表。比如：采用分區表,删除或者增加字段等，建立中間表！

20:22:14 yang(45)@yangdb> create table rbol_tmp (id number primary key,time date,val varchar2(10));

20:22:36 yang(45)@yangdb> exec dbms_redefinition.can_redef_table(user, 'rbol', dbms_redefinition.cons_use_pk);

20:22:53 yang(45)@yangdb> exec dbms_redefinition.start_redef_table(user,'rbol','rbol_tmp');

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;

     73488

20:25:43 yang(32)@yangdb> commit;

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');

20:26:06 yang(32)@yangdb> select count(1) from rbol_tmp;

20:26:13 yang(32)@yangdb> select count(1) from rbol;    

7 結束重定義步驟！

20:25:25 yang(45)@yangdb> exec dbms_redefinition.finish_redef_table(user,'rbol','rbol_tmp');

20:26:43 yang(32)@yangdb> desc rbol

 name   null?   type

 ------ ------- --------

 id    not null number

 time           date

 val            varchar2(10)