線上重定義[@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.

Oracle線上重定義

相關文章