之前的兩篇博文中，列舉了兩個單表線上重定義的過程：
Online Redefinition線上重定義（一）:http://blog.itpub.net/31015730/viewspace-2144544/
Online Redefinition線上重定義（二）--單表複雜案例 : http://blog.itpub.net/31015730/viewspace-2144603/

但是在生產環境下，很多表之間都是有關聯關係，本篇我們就來看一下如何對多個關聯表進行線上重定義重定義，將普通表改造成分割槽表

需求：將外來鍵關聯的兩張表進行線上重定義，改造成分割槽表

--首先建立兩張實驗資料表t_wjq2_master和t_wjq2_slave。

SEIANG@seiang11g>create table t_wjq2_master as select owner,table_name,tablespace_name,status from dba_tables where 1=2;

Table created.

--在表t_wjq2_master的owner和table_name列上建立主鍵約束

SEIANG@seiang11g>alter table t_wjq2_master add constraint pk_t_wjq2_master primary key(owner,table_name);

Table altered.

SEIANG@seiang11g>create table t_wjq2_slave as select owner,table_name,column_name from dba_tab_columns where 1=2;

Table created.

--在表t_wjq2_slave的owner、table_name和column_name列上建立主鍵約束

SEIANG@seiang11g>alter table t_wjq2_slave add constraint pk_t_wjq2_slave primary key(owner,table_name,column_name);

Table altered.

--分別在表t_wjq2_master和t_wjq2_slave中插入資料

SEIANG@seiang11g>insert into t_wjq2_master select owner,table_name,tablespace_name,status from dba_tables;

2881 rows created.

SEIANG@seiang11g>

SEIANG@seiang11g>insert into t_wjq2_slave select owner,table_name,column_name from dba_tab_columns where (owner,table_name) in (select owner,table_name from dba_tables);

31434 rows created.

--在t_wjq2_slave上建立外來鍵約束，參考t_wjq2_master表，是的這兩張表主子表關係

SEIANG@seiang11g>alter table t_wjq2_slave add constraint fk_t_wjq2_slave foreign key(owner,table_name) references t_wjq2_master(owner,table_name);

Table altered.

--檢視錶上的約束

SEIANG@seiang11g>select owner,constraint_name,constraint_type,table_name from user_constraints;

OWNER CONSTRAINT_NAME C TABLE_NAME

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

SEIANG FK_T_WJQ2_SLAVE R T_WJQ2_SLAVE

SEIANG PK_T_WJQ2_SLAVE P T_WJQ2_SLAVE

SEIANG PK_T_WJQ2_MASTER P T_WJQ2_MASTER

SEIANG SYS_C0011655 C T_WJQ2_MASTER

SEIANG SYS_C0011656 C T_WJQ2_MASTER

SEIANG SYS_C0011658 C T_WJQ2_SLAVE

SEIANG SYS_C0011659 C T_WJQ2_SLAVE

SEIANG SYS_C0011660 C T_WJQ2_SLAVE

--建立中間表，主要目標是將資料表按照owner進行分割槽，轉化為分割槽表

SEIANG@seiang11g>create table t_wjq2_master_interim(

2 OWNER VARCHAR2(30),

3 TABLE_NAME VARCHAR2(30),

4 TABLESPACE_NAME VARCHAR2(30),

5 STATUS VARCHAR2(8)

6 )

7 partition by list(owner)

8 (

9 partition p1 values ('SYS'),

10 partition p2 values (default)

11 );

Table created.

Elapsed: 00:00:00.02

SEIANG@seiang11g>create table t_wjq2_slave_interim(

2 owner varchar2(30),

3 table_name varchar2(30),

4 column_name varchar2(30)

5 )

6 partition by list(owner)

7 (

8 partition p1 values ('SYS'),

9 partition p2 values (default)

10 );

Table created.

Elapsed: 00:00:00.02

--判斷兩張表t_wjq2_master和t_wjq2_slave是否可以進行線上重定義

SEIANG@seiang11g>exec dbms_redefinition.can_redef_table('SEIANG','T_WJQ2_MASTER',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SEIANG@seiang11g>exec dbms_redefinition.can_redef_table('SEIANG','T_WJQ2_SLAVE',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

注：如果從安全和順序關係看，應該是先子表後主表似乎好一點

--開始線上重定義，首先對t_wjq2_master表進行重定義

SEIANG@seiang11g>exec dbms_redefinition.start_redef_table('SEIANG','T_WJQ2_MASTER','T_WJQ2_MASTER_INTERIM',col_mapping => 'owner owner, table_name table_name, tablespace_name tablespace_name, status status',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.98

--驗證資料是否已經刷過去了

SEIANG@seiang11g>select count(*) from t_wjq2_master;

COUNT(*)

----------

2881

Elapsed: 00:00:00.00

SEIANG@seiang11g>

SEIANG@seiang11g>select count(*) from t_wjq2_master_interim;

COUNT(*)

----------

2881

Elapsed: 00:00:00.01

--執行表同步

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

SEIANG@seiang11g> exec dbms_redefinition.sync_interim_table('SEIANG','T_WJQ2_MASTER','T_WJQ2_MASTER_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

SEIANG@seiang11g>declare

2 error_count number:=0;

3 begin

4 dbms_redefinition.copy_table_dependents(

5 uname => 'SEIANG',orig_table => 'T_WJQ2_MASTER',

6 int_table => 'T_WJQ2_MASTER_INTERIM',

7 copy_indexes => dbms_redefinition.cons_orig_params,

8 num_errors => error_count);

9 end;

10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.39

SEIANG@seiang11g> exec dbms_redefinition.finish_redef_table('SEIANG','T_WJQ2_MASTER','T_WJQ2_MASTER_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.38

--下面對t_wjq2_slave表進行線上重定義

SEIANG@seiang11g> exec dbms_redefinition.start_redef_table('SEIANG','T_WJQ2_SLAVE','T_WJQ2_SLAVE_INTERIM',col_mapping => 'owner owner, table_name table_name, column_name column_name',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31

SEIANG@seiang11g>select count(*) from t_wjq2_slave;

COUNT(*)

----------

31434

Elapsed: 00:00:00.00

SEIANG@seiang11g>

SEIANG@seiang11g>select count(*) from t_wjq2_slave_interim;

COUNT(*)

----------

31434

Elapsed: 00:00:00.01

SEIANG@seiang11g>exec dbms_redefinition.sync_interim_table('SEIANG','T_WJQ2_SLAVE','T_WJQ2_SLAVE_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SEIANG@seiang11g>declare

2 error_count number:=0;

3 begin

4 dbms_redefinition.copy_table_dependents(

5 uname => 'SEIANG',orig_table => 'T_WJQ2_SLAVE',

6 int_table => 'T_WJQ2_SLAVE_INTERIM',

7 copy_indexes => dbms_redefinition.cons_orig_params,

8 num_errors => error_count);

9 end;

10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.68

SEIANG@seiang11g>exec dbms_redefinition.finish_redef_table('SEIANG','T_WJQ2_SLAVE','T_WJQ2_SLAVE_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.60

--驗證重定義結果，檢視分割槽的情況

SEIANG@seiang11g>select table_name, partition_name from dba_tab_partitions where table_owner='SEIANG' and table_name in ('T_WJQ2_MASTER','T_WJQ2_SLAVE');

TABLE_NAME PARTITION_NAME

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

T_WJQ2_MASTER P1

T_WJQ2_MASTER P2

T_WJQ2_SLAVE P1

T_WJQ2_SLAVE P2

--檢視約束的情況

SEIANG@seiang11g>select owner,constraint_name,constraint_type,table_name,status from user_constraints where table_name like 'T_WJQ2%';

OWNER CONSTRAINT_NAME C TABLE_NAME STATUS

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

SEIANG SYS_C0011660 C T_WJQ2_SLAVE ENABLED

SEIANG SYS_C0011659 C T_WJQ2_SLAVE ENABLED

SEIANG SYS_C0011658 C T_WJQ2_SLAVE ENABLED

SEIANG TMP$$_SYS_C00116580 C T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG SYS_C0011656 C T_WJQ2_MASTER ENABLED

SEIANG SYS_C0011655 C T_WJQ2_MASTER ENABLED

SEIANG TMP$$_SYS_C00116560 C T_WJQ2_MASTER_INTERIM ENABLED

SEIANG TMP$$_SYS_C00116550 C T_WJQ2_MASTER_INTERIM ENABLED

SEIANG TMP$$_SYS_C00116590 C T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG TMP$$_SYS_C00116600 C T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG FK_T_WJQ2_SLAVE R T_WJQ2_SLAVE ENABLED

SEIANG PK_T_WJQ2_SLAVE P T_WJQ2_SLAVE ENABLED

SEIANG PK_T_WJQ2_MASTER P T_WJQ2_MASTER ENABLED

SEIANG TMP$$_PK_T_WJQ2_MASTER0 P T_WJQ2_MASTER_INTERIM ENABLED

SEIANG TMP$$_PK_T_WJQ2_SLAVE0 P T_WJQ2_SLAVE_INTERIM ENABLED

SEIANG TMP$$_FK_T_WJQ2_SLAVE0 R T_WJQ2_SLAVE DISABLED

SEIANG TMP$$_FK_T_WJQ2_SLAVE1 R T_WJQ2_SLAVE_INTERIM DISABLED

SEIANG TMP$$_TMP$$_FK_T_WJQ2_SLAVE0 R T_WJQ2_SLAVE_INTERIM DISABLED

重定義成功。

Oracle線上重定義是一種非常強大的定義工具。透過三篇文章的幾個簡單案例介紹了線上重定義最常用的一些流程和方法。其他一些諸如register物件和重新命名的方法，在一些特定場合下有比較好的使用空間。詳細的使用方法請參考另一篇博文：Oracle線上重定義之DBMS_REDEFINITION:http://blog.itpub.net/31015730/viewspace-2144516/

在使用線上重定義的時候，需要注意以下幾點：
1、如果離線操作能夠解決問題，就不要用線上重定義；例如一些靜態資料、歷史資料的歸檔遷移，可使用CTAS、alter table move…、或匯出匯入完成
2、表空間至少要留有比源表所用空間更大的剩餘空間
3、線上重定義的操作過程耗時較長，但對業務的影響最小
4、要注意源表上的事務操作，如果過於頻繁，可能會發生較嚴重的等待

應該說，Oracle線上重定義是一種平滑效能、減少鎖定、提高系統整體可用性的解決方案。從操作時間和空間消耗上，線上重定義並不具有很高的優勢。對於7*24小時的系統，該特性是一種不錯的選擇。

參考連結：

http://blog.itpub.net/11676357/viewspace-1052296/

http://www.cnblogs.com/flowerszhong/p/4535206.html

作者：SEian.G（苦練七十二變，笑對八十一難）

Online Redefinition線上重定義（三）--多表關聯重定義案例

相關文章