本篇文章為大家展示了怎么進(jìn)行Oracle Online Redefinition在線(xiàn)重定義，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。

創(chuàng)新互聯(lián)專(zhuān)注于中大型企業(yè)的成都網(wǎng)站建設(shè)、成都網(wǎng)站制作和網(wǎng)站改版、網(wǎng)站營(yíng)銷(xiāo)服務(wù)，追求商業(yè)策劃與數(shù)據(jù)分析、創(chuàng)意藝術(shù)與技術(shù)開(kāi)發(fā)的融合，累計(jì)客戶(hù)超過(guò)千家，服務(wù)滿(mǎn)意度達(dá)97%。幫助廣大客戶(hù)順利對(duì)接上互聯(lián)網(wǎng)浪潮，準(zhǔn)確優(yōu)選出符合自己需要的互聯(lián)網(wǎng)運(yùn)用，我們將一直專(zhuān)注品牌網(wǎng)站設(shè)計(jì)和互聯(lián)網(wǎng)程序開(kāi)發(fā)，在前進(jìn)的路上，與客戶(hù)一起成長(zhǎng)！

在之前的文章中，我們看到了如何處理單表在線(xiàn)重定義過(guò)程。

4、外鍵關(guān)系表重定義

 

我們先創(chuàng)建出實(shí)驗(yàn)數(shù)據(jù)表。

 

 

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構(gòu)成主子表關(guān)系，插入數(shù)據(jù)。

 

 

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

 

 

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

 

 

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

 

 

進(jìn)入正式的重定義流程。這個(gè)過(guò)程，如果處于安全和順序關(guān)系看，應(yīng)該是先子表后主表似乎好一點(diǎn)。筆者選擇了先主表后子表的方法。

 

 

--判斷是否可以進(jìn)行在線(xiàn)重定義過(guò)程；

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表進(jìn)行重定義過(guò)程。

 

 

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

 

 

進(jìn)行T_SLAVE表重定義過(guò)程。

 

 

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

 

 

最后，我們檢查處理結(jié)果。

 

 

--分區(qū)處理成功；

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

 

 

由于是分別進(jìn)行的重定義動(dòng)作，中間可能有關(guān)聯(lián)裹挾的情況，所以需要額外進(jìn)行一些處理。主要目標(biāo)是將Interim數(shù)據(jù)表刪除掉。

 

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、結(jié)論和討論

Oracle在線(xiàn)重定義是一種非常強(qiáng)大的定義工具。這個(gè)系列只是介紹了該特性中最常用的一些流程和方法。其他一些諸如register對(duì)象和重命名的方法，在一些特定場(chǎng)合下有比較好的使用空間。

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

上述內(nèi)容就是怎么進(jìn)行Oracle Online Redefinition在線(xiàn)重定義，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

本文標(biāo)題：怎么進(jìn)行OracleOnlineRedefinition在線(xiàn)重定義
標(biāo)題鏈接：http://aaarwkj.com/article14/jpooge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計(jì)、服務(wù)器托管、企業(yè)建站、網(wǎng)頁(yè)設(shè)計(jì)公司、ChatGPT、電子商務(wù)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)