Oracle Online Redefinition在线重定义（下）
在之前的文章中，我們看到了如何處理單表在線重定義過程。本篇我們來看一下如何進行關聯表的重定義過程。
?
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在线重定义（下）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Ibatis2到mybatis3的转变1
- 下一篇： android单独刷入镜像