在之前的文章中，我們看到了如何處理單表線上重定義過程。本篇我們來看一下如何進行關聯表的重定義過程。

4、外來鍵關係表重定義

我們先建立出實驗資料表。

SQL> create table t_master as select owner, table_name, tablespace_name, status from dba_tables where 1=0;

Table created

SQL> alter table t_master add constraint pk_t_master primary key (owner, table_name);

Table altered

Executed in 0.125 seconds

SQL> create table t_slave as select owner, table_name, column_name from dba_tab_columns where 1=0;

Table created

SQL> alter table T_SLAVE

2 add constraint pk_t_slave primary key (OWNER, TABLE_NAME, COLUMN_NAME);

Table altered

Executed in 0.422 seconds

T_MASTER和T_SLAVE構成主子表關係，插入資料。

SQL> insert into t_master select owner, table_name, tablespace_name, status from dba_tables;

2841 rows inserted

Executed in 0.157 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> insert into t_slave select owner, table_name, column_name from dba_tab_cols where (owner, table_name) in (select owner, table_name from dba_tables);

32388 rows inserted

Executed in 2.328 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> alter table T_SLAVE

2 add constraint fk_t_slave_master foreign key (OWNER, TABLE_NAME)

3 references t_master (OWNER, TABLE_NAME);

Table altered

Executed in 0.266 seconds

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

SQL> create table t_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 )

12 ;

Table created

Executed in 0.156 seconds

SQL> create table t_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 )

11 ;

Table created

Executed in 0.032 seconds

進入正式的重定義流程。這個過程，如果處於安全和順序關係看，應該是先子表後主表似乎好一點。筆者選擇了先主表後子表的方法。

--判斷是否可以進行線上重定義過程；

SQL> set serveroutput on;

SQL> exec dbms_redefinition.can_redef_table('SCOTT','T_MASTER',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed

Executed in 0.172 seconds

SQL> exec dbms_redefinition.can_redef_table('SCOTT','T_SLAVE',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed

Executed in 0.015 seconds

T_MASTER表進行重定義過程。

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T_MASTER','T_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

Executed in 1.125 seconds

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T_MASTER','T_MASTER_INTERIM');

PL/SQL procedure successfully completed

Executed in 0.047 seconds

SQL>

SQL> set serveroutput on;

SQL> declare

2 error_count number:=0;

3 begin

4 dbms_redefinition.copy_table_dependents(uname => 'SCOTT',orig_table => 'T_MASTER',

5 int_table => 'T_MASTER_INTERIM',

6 copy_indexes => dbms_redefinition.cons_orig_params,

7 num_errors => error_count);

8 dbms_output.put_line(to_char(error_count));

9 end;

10 /

0

PL/SQL procedure successfully completed

Executed in 6.766 seconds

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T_MASTER','T_MASTER_INTERIM');

PL/SQL procedure successfully completed

Executed in 1.75 seconds

進行T_SLAVE表重定義過程。

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T_SLAVE','T_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

Executed in 1.484 seconds

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T_SLAVE','T_SLAVE_INTERIM');

PL/SQL procedure successfully completed

Executed in 0.047 seconds

SQL>

SQL> set serveroutput on;

SQL> declare

2 error_count number:=0;

3 begin

4 dbms_redefinition.copy_table_dependents(uname => 'SCOTT',orig_table => 'T_SLAVE',

5 int_table => 'T_SLAVE_INTERIM',

6 copy_indexes => dbms_redefinition.cons_orig_params,

7 num_errors => error_count);

8 dbms_output.put_line(to_char(error_count));

9 end;

10 /

0

PL/SQL procedure successfully completed

Executed in 6.718 seconds

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T_SLAVE','T_SLAVE_INTERIM');

PL/SQL procedure successfully completed

Executed in 1.75 seconds

最後，我們檢查處理結果。

--分割槽處理成功；

SQL> select table_name, partition_name from dba_tab_partitions where table_owner='SCOTT' and table_name in ('T_MASTER','T_SLAVE');

TABLE_NAME PARTITION_NAME

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

T_MASTER P1

T_MASTER P2

T_SLAVE P1

T_SLAVE P2

Executed in 0.031 seconds

約束中存在一些需要額外處理的地方。

SQL> select constraint_name, constraint_type, R_CONSTRAINT_NAME from dba_constraints where wner='SCOTT' and table_name in ('T_MASTER','T_SLAVE');

CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME

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

TMP$$_FK_T_SLAVE_MASTER0 R TMP$$_PK_T_MASTER0

SYS_C0011276 C

SYS_C0011275 C

SYS_C0011274 C

PK_T_SLAVE P

FK_T_SLAVE_MASTER R PK_T_MASTER

SYS_C0011272 C

SYS_C0011271 C

PK_T_MASTER P

9 rows selected

Executed in 0.141 seconds

由於是分別進行的重定義動作，中間可能有關聯裹挾的情況，所以需要額外進行一些處理。主要目標是將Interim資料表刪除掉。

SQL> drop table t_slave_interim;

Table dropped

Executed in 0.438 seconds

SQL> alter table t_slave drop constraint "TMP$$_FK_T_SLAVE_MASTER0";

Table altered

Executed in 0.031 seconds

SQL> drop table t_master_interim purge;

Table dropped

Executed in 0.094 seconds

檢查約束情況。

SQL> select constraint_name, constraint_type, R_CONSTRAINT_NAME from dba_constraints where wner='SCOTT' and table_name in ('T_MASTER','T_SLAVE');

CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME

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

PK_T_MASTER P

PK_T_SLAVE P

FK_T_SLAVE_MASTER R PK_T_MASTER

SYS_C0011271 C

SYS_C0011272 C

SYS_C0011274 C

SYS_C0011275 C

SYS_C0011276 C

8 rows selected

Executed in 0.125 seconds

重定義成功。

5、結論和討論

Oracle線上重定義是一種非常強大的定義工具。這個系列只是介紹了該特性中最常用的一些流程和方法。其他一些諸如register物件和重新命名的方法，在一些特定場合下有比較好的使用空間。

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

Oracle Online Redefinition線上重定義（下）

相關文章