---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

ITPub博客

oracle在线重定义表步骤

最新文章