---1 設定並行(可選項)

SQL> alter session force parallel dml parallel 20;

Session altered

SQL> alter session force parallel query parallel 20;

Session altered

---2 建立中間表(為了演示包含了原始表)

drop table tab1_child;

drop table tab1;

drop table tab1_inte;

drop table tab1_parent;

create table tab1(id number,name varchar2(20),p_id number);

create table tab1_inte(id number,name varchar2(20),p_id number);

create table tab1_parent(id number,name varchar2(20));

create table tab1_child(id number,name varchar2(20),p_id number);

insert into tab1 values(1,'a',1);

insert into tab1 values(2,'b',2);

insert into tab1_parent values(1,'a_p');

insert into tab1_parent values(2,'b_p');

insert into tab1_child values(1,'a_c',1);

insert into tab1_child values(2,'b_c',1);

commit;

create index tab1_indx on TAB1 (name);

alter table TAB1

add constraint tab1_pk primary key (ID);

;

alter table TAB1_PARENT

add constraint tab1_parent_pk primary key (ID);

alter table TAB1 add constraint tab1_con foreign key (P_ID)

references tab1_parent (ID);

alter table TAB1_CHILD

add constraint tab1_child_pk primary key (ID);

alter table TAB1_CHILD

add constraint tab1_child_con foreign key (P_ID)

references tab1 (ID);

----3查詢原表是否可以線上重定義(根據主鍵或rowid)

options_flag

--- dbms_redefinition.cons_use_pk

--- dbms_redefinition.cons_use_rowid

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('GFOLTP', 'TAB1', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed

--- 4啟動線上重定義

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('GFOLTP', 'TAB1','TAB1_INTE');

PL/SQL procedure successfully completed

---5複製依賴物件(約束,索引,觸發器,許可權,統計資訊)

線上重定義的原理是rename, 所以需要此步驟

SQL> DECLARE

2 num_errors PLS_INTEGER;

3 begin

4 DBMS_REDEFINITION.copy_table_dependents(uname=> 'GFOLTP',

5 orig_table => 'TAB1',

6 int_table => 'TAB1_INTE',

7 num_errors => num_errors,copy_statistics => true);

8 end;

9 /

PL/SQL procedure successfully completed

----6 同步資料(可選項 資料變化的情況下使用)

SQL> EXEC dbms_redefinition.sync_interim_table(uname=> 'GFOLTP',orig_table => 'TAB1',int_table => 'TAB1_INTE');

PL/SQL procedure successfully completed

--- 7 完成現在重定義

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('GFOLTP', 'TAB1','TAB1_INTE');

PL/SQL procedure successfully completed

oracle線上重定義表步驟

相關文章