在之前的文章中，我们看到了如何处理单表在线重定义过程。本篇我们来看一下如何进行关联表的重定义过程。
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小时的系统，该特性是一种不错的选择。