Oracle線上重定義

Oracle線上重定義

線上重定義[@more@]Oracle9i之前，表資料的整理是通過 alter table XXX move [tablespace XX]進行的。如果表非常大，IO又不快的時候，move的過程可能相當漫長，不能算是HA特性。因此在HA的線上維護中，基本不會利用move來重組大型表，而且move後的表需要重建索引。而在9i引入的dbms_redefinition。該過程的內部原理其實就是採用了MV的機制，類似在原表建立一個prebuilt的MV然後一直增量重新整理到資料差別最小。Oracle在很小的停頓中，完成最後一點的增量同步處理，達到完全同步後，把原表與新表換個名字，由於換名操作僅僅是資料字典，所以最終切換時間非常短。

--首先建立一個用來測試外鍵約束的表test_refed

SQL> create table test_refed(id number primary key);

Table created.

SQL> insert into test_refed select rownum from dba_objects where rownum<10001;

10000 rows created.

SQL> commit;

Commit complete.

--建立我們準備修改表定義的表test，未分區

SQL> create table test(id number,fid number);

Table created.

SQL> insert into test select rownum,rownum from dba_objects where rownum<1001;

1000 rows created.

SQL> commit;

Commit complete.

--添加主鍵約束以及外鍵約束，並在外鍵上建立索引

SQL> alter table test add constraint pk_test primary key(id);

Table altered.

SQL> alter table test add constraint fk_test_refed_id foreign key(fid) references test_refed(id);

Table altered.

SQL> create index idx_test_fid on test(fid);

Index created.

--建立基於test表的觸發器

SQL> create trigger tr_test
 2 before insert on test for each row
 3 begin
 4 null;
 5 end;
 6 /

Trigger created.

--準備工作：調用dbms_redefinition.can_redef_table來驗證boylook.test是否可以線上重定義。一般情況沒有主鍵等會報錯。
SQL> begin
 2 dbms_redefinition.can_redef_table('BOYLOOK','TEST');
 3 end;
 4 /

PL/SQL procedure successfully completed.

--建立需要重新定義的過渡表inter_test，這是一個分區表，以後將把原表所有資料線上轉移到該表當中。注意到，該表比原表test還多一個欄位c

SQL> create table inter_test(id number,fid number,c number)
 2 partition by range(id)
 3 (partition p1 values less than(400),
 4 partition p2 values less than(800),
 5 partition p3 values less than(maxvalue));

Table created.

SQL> alter table inter_test add constraint pk_inter_test primary key(id);

Table altered.

--執行線上重定義
註：調用這個預存程序需要create/alter/drop/lock/select any table許可權

SQL> exec dbms_redefinition.start_redef_table('BOYLOOK','TEST','INTER_TEST','id id,fid fid,0 c');

PL/SQL procedure successfully completed.

--驗證資料是否刷了過去
SQL> select count(*) from inter_test;

 COUNT(*)
----------
 1000

--繼續對原表test進行操作
SQL> insert into test select rownum+1000,rownum+1000 from dba_objects where rownum <=24;

24 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

 COUNT(*)
----------
 1024

SQL> select count(*) from inter_test;

 COUNT(*)
----------
 1000

--執行表同步
註：這一步不是必須的，但是對於比較大的表，中間運行增量同步處理有助於減少切換時間。

SQL> exec dbms_redefinition.sync_interim_table('BOYLOOK','TEST','INTER_TEST');

PL/SQL procedure successfully completed.

--我們探索資料同步的過來
SQL> select count(*) from inter_test;

 COUNT(*)
----------
 1024

--將原表test的約束，索引，觸發器遷移過來
註：這裡最好要檢查一下授權。檢查test以前的許可權，並給中間表inter_test賦予同樣的許可權。否則原表的許可權不會轉移到新表
SQL> alter table inter_test add constraint fk_inter_refed_id foreign key(fid) references test_refed(id);

Table altered.

SQL> create index idx_inter_test_fid on inter_test(fid);

Index created.

SQL> create or replace trigger tr_inter_test
 2 before insert on inter_test for each row
 3 begin
 4 null;
 5 end;
 6 /

Trigger created.

--執行重定義完成的過程。Oracle完成了表test到表inter_test的換名工作，只是所有的約束，索引或觸發器名稱還是保持著原來表上面的名稱

SQL> exec dbms_redefinition.finish_redef_table('BOYLOOK','TEST','INTER_TEST');

PL/SQL procedure successfully completed.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FID NUMBER
C NUMBER

SQL> desc inter_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FID NUMBER

SQL> select table_name,partition_name from user_tab_partitions where table_name ='TEST';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST P1
TEST P2
TEST P3

SQL> select table_name,constraint_name,status from user_constraints where table_name in('TEST','INTER_TEST');

TABLE_NAME CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ --------
INTER_TEST PK_TEST ENABLED
INTER_TEST FK_TEST_REFED_ID DISABLED
TEST PK_INTER_TEST ENABLED
TEST FK_INTER_REFED_ID ENABLED

SQL> select table_name,index_name from user_indexes where table_name in('TEST','INTER_TEST');

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TEST PK_INTER_TEST
TEST IDX_INTER_TEST_FID
INTER_TEST PK_TEST
INTER_TEST IDX_TEST_FID

SQL> select table_name,trigger_name from user_triggers where table_name in('TEST','INTER_TEST');

TABLE_NAME TRIGGER_NAME
------------------------------ ------------------------------
TEST TR_INTER_TEST
INTER_TEST TR_TEST

SQL> select * from test where rownum <=10;

 ID FID C
---------- ---------- ----------
 1 1 0
 2 2 0
 3 3 0
 4 4 0
 5 5 0
 6 6 0
 7 7 0
 8 8 0
 9 9 0
 10 10 0

10 rows selected.

--刪除過渡表了，收工。這時也可以考慮修改索引，約束觸發器的名稱與原來保持一致

SQL> drop table inter_test cascade constraints purge;

Table dropped.