Online Redefinition在線(xiàn)重定義（三）--多表關(guān)聯(lián)重定義案例

之前的兩篇博文中，列舉了兩個(gè)單表在線(xiàn)重定義的過(guò)程：
Online Redefinition在線(xiàn)重定義（一）:http://blog.itpub.net/31015730/viewspace-2144544/
Online Redefinition在線(xiàn)重定義（二）--單表復(fù)雜案例 : http://blog.itpub.net/31015730/viewspace-2144603/

但是在生產(chǎn)環(huán)境下，很多表之間都是有關(guān)聯(lián)關(guān)系，本篇我們就來(lái)看一下如何對(duì)多個(gè)關(guān)聯(lián)表進(jìn)行在線(xiàn)重定義重定義，將普通表改造成分區(qū)表

需求：將外鍵關(guān)聯(lián)的兩張表進(jìn)行在線(xiàn)重定義，改造成分區(qū)表

--首先創(chuàng)建兩張實(shí)驗(yàn)數(shù)據(jù)表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列上創(chuàng)建主鍵約束

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列上創(chuàng)建主鍵約束

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中插入數(shù)據(jù)

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上創(chuàng)建外鍵約束，參考t_wjq2_master表，是的這兩張表主子表關(guān)系

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

--創(chuàng)建中間表，主要目標(biāo)是將數(shù)據(jù)表按照owner進(jìn)行分區(qū)，轉(zhuǎn)化為分區(qū)表

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是否可以進(jìn)行在線(xiàn)重定義

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

注：如果從安全和順序關(guān)系看，應(yīng)該是先子表后主表似乎好一點(diǎn)

--開(kāi)始在線(xiàn)重定義，首先對(duì)t_wjq2_master表進(jìn)行重定義

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

--驗(yàn)證數(shù)據(jù)是否已經(jīng)刷過(guò)去了

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

--執(zhí)行表同步

注：這一步不是必須的，但是對(duì)于比較大的表，中間運(yùn)行增量同步有助于減少切換時(shí)間

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

--下面對(duì)t_wjq2_slave表進(jìn)行在線(xiàn)重定義

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

--驗(yàn)證重定義結(jié)果，查看分區(qū)的情況

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在線(xiàn)重定義是一種非常強(qiáng)大的定義工具。通過(guò)三篇文章的幾個(gè)簡(jiǎn)單案例介紹了在線(xiàn)重定義最常用的一些流程和方法。其他一些諸如register對(duì)象和重命名的方法，在一些特定場(chǎng)合下有比較好的使用空間。詳細(xì)的使用方法請(qǐng)參考另一篇博文：Oracle在線(xiàn)重定義之DBMS_REDEFINITION:http://blog.itpub.net/31015730/viewspace-2144516/

在使用在線(xiàn)重定義的時(shí)候，需要注意以下幾點(diǎn)：
1、如果離線(xiàn)操作能夠解決問(wèn)題，就不要用在線(xiàn)重定義；例如一些靜態(tài)數(shù)據(jù)、歷史數(shù)據(jù)的歸檔遷移，可使用CTAS、alter table move…、或?qū)С鰧?dǎo)入完成
2、表空間至少要留有比源表所用空間更大的剩余空間
3、在線(xiàn)重定義的操作過(guò)程耗時(shí)較長(zhǎng)，但對(duì)業(yè)務(wù)的影響最小
4、要注意源表上的事務(wù)操作，如果過(guò)于頻繁，可能會(huì)發(fā)生較嚴(yán)重的等待

應(yīng)該說(shuō)，Oracle在線(xiàn)重定義是一種平滑性能、減少鎖定、提高系統(tǒng)整體可用性的解決方案。從操作時(shí)間和空間消耗上，在線(xiàn)重定義并不具有很高的優(yōu)勢(shì)。對(duì)于7*24小時(shí)的系統(tǒng)，該特性是一種不錯(cuò)的選擇。

參考鏈接：

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

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