南京Oracle认证培训 Oracle在线重定义

　　Oracle 9i之前，表数据的整理是通过 alter table XXX move [tablespace XX]进行的。如果表非常大，IO又不快的时候，move的过程可能相当漫长，不能算是HA特性。因此在HA的在线维护中，基本不会利用move来重组大型表，而且move后的表需要重建索引。而在9i引入的dbms_redefinition。该过程的内部原理其实就是采用了MV的机制，类似在原表建立一个prebuilt的MV然后一直增量刷新到数据差别最小。Oracle在很小的停顿中，完成最后一点的增量同步，达到完全同步后，把原表与新表换个名字，由于换名操作仅仅是数据字典，所以最终切换时间非常短。南京Oracle认证培训

　　--首先建立一个用来测试外键约束的表test_refed

　　SQL> create table test_refed(id number primary key);

　　Table created.

　　SQL> insert into test_refed select rownum from dba_objects where rownum<10001;

　　10000 rows created.

　　SQL> commit;

　　Commit complete.

　　--创建我们准备修改表定义的表test，未分区

　　SQL> create table test(id number,fid number);

　　Table created.

　　SQL> insert into test select rownum,rownum from dba_objects where rownum<1001;

　　1000 rows created.

　　SQL> commit;

　　Commit complete.

　　--添加主键约束以及外键约束，并在外键上建立索引。南京Oracle认证培训

　　SQL> alter table test add constraint pk_test primary key(id);

　　Table altered.

　　SQL> alter table test add constraint fk_test_refed_id foreign key(fid) references test_refed(id);

　　Table altered.

　　SQL> create index idx_test_fid on test(fid);

　　Index created.

　　--创建基于test表的触发器

　　SQL> create trigger tr_test

　　2 before insert on test for each row

　　3 begin

　　4 null;

　　5 end;

　　6 /

　　Trigger created.

　　--准备工作：调用dbms_redefinition.can_redef_table来验证boylook.test是否可以在线重定义。一般情况没有主键等会报错。南京Oracle认证培训

　　SQL> begin

　　2 dbms_redefinition.can_redef_table('BOYLOOK','TEST');

　　3 end;

　　4 /

　　PL/SQL procedure successfully completed.

　　--创建需要重新定义的过渡表inter_test，这是一个分区表，以后将把原表所有数据在线转移到该表当中。注意到，该表比原表test还多一个字段c 南京Oracle认证培训

　　SQL> create table inter_test(id number,fid number,c number)

　　2 partition by range(id)

　　3 (partition p1 values less than(400),

　　4 partition p2 values less than(800),

　　5 partition p3 values less than(maxvalue));

　　Table created.

　　SQL> alter table inter_test add constraint pk_inter_test primary key(id);

　　Table altered.

　　--执行在线重定义

　　注：调用这个存储过程需要create/alter/drop/lock/select any table权限

　　SQL> exec dbms_redefinition.start_redef_table('BOYLOOK','TEST','INTER_TEST','id id,fid fid,0 c');

　　PL/SQL procedure successfully completed。

　　--验证数据是否刷了过去

　　SQL> select count(*) from inter_test;

　　COUNT(*)

　　----------

　　1000

　　--继续对原表test进行操作

　　SQL> insert into test select rownum+1000,rownum+1000 from dba_objects where rownum <=24;

　　24 rows created.

　　SQL> commit;

　　Commit complete.

　　SQL> select count(*) from test;

　　COUNT(*)

　　----------

　　1024

　　SQL> select count(*) from inter_test;

　　COUNT(*)

　　----------

　　1000

　　--执行表同步

　　注：这一步不是必须的，但是对于比较大的表，中间运行增量同步有助于减少切换时间。南京Oracle认证培训

　　SQL> exec dbms_redefinition.sync_interim_table('BOYLOOK','TEST','INTER_TEST');

　　PL/SQL procedure successfully completed.

　　--我们发现数据同步的过来

　　SQL> select count(*) from inter_test;

　　COUNT(*)

　　----------

　　1024

　　--将原表test的约束，索引，触发器迁移过来

　　注：这里最好要检查一下授权。检查test以前的权限，并给中间表inter_test赋予同样的权限。否则原表的权限不会转移到新表。南京Oracle认证培训

　　SQL> alter table inter_test add constraint fk_inter_refed_id foreign key(fid) references test_refed(id);

　　Table altered.

　　SQL> create index idx_inter_test_fid on inter_test(fid);

　　Index created.

　　SQL> create or replace trigger tr_inter_test

　　2 before insert on inter_test for each row

　　3 begin

　　4 null;

　　5 end;

　　6 /

　　Trigger created.

　　--执行重定义完成的过程。Oracle完成了表test到表inter_test的换名工作，只是所有的约束，索引或触发器名称还是保持着原来表上面的名称。南京Oracle培训

　　SQL> exec dbms_redefinition.finish_redef_table('BOYLOOK','TEST','INTER_TEST');

　　PL/SQL procedure successfully completed.

　　SQL> desc test;

　　Name Null? Type

　　----------------------------------------- -------- ----------------------------

　　ID NOT NULL NUMBER

　　FID NUMBER

　　C NUMBER

　　SQL> desc inter_test;

　　Name Null? Type

　　----------------------------------------- -------- ----------------------------

　　ID NOT NULL NUMBER

　　FID NUMBER

　　SQL> select table_name,partition_name from user_tab_partitions where table_name ='TEST';

　　TABLE_NAME PARTITION_NAME

　　------------------------------ ------------------------------

　　TEST P1

　　TEST P2

　　TEST P3

　　SQL> select table_name,constraint_name,status from user_constraints where table_name in('TEST','INTER_TEST');

　　TABLE_NAME CONSTRAINT_NAME STATUS

　　------------------------------ ------------------------------ --------

　　INTER_TEST PK_TEST ENABLED

　　INTER_TEST FK_TEST_REFED_ID DISABLED

　　TEST PK_INTER_TEST ENABLED

　　TEST FK_INTER_REFED_ID ENABLED

　　SQL> select table_name,index_name from user_indexes where table_name in('TEST','INTER_TEST');

　　TABLE_NAME INDEX_NAME

　　------------------------------ ------------------------------

　　TEST PK_INTER_TEST

　　TEST IDX_INTER_TEST_FID

　　INTER_TEST PK_TEST

　　INTER_TEST IDX_TEST_FID

　　SQL> select table_name,trigger_name from user_triggers where table_name in('TEST','INTER_TEST');南京Oracle认证培训

　　TABLE_NAME TRIGGER_NAME

　　------------------------------ ------------------------------

　　TEST TR_INTER_TEST

　　INTER_TEST TR_TEST

　　SQL> select * from test where rownum <=10;

　　ID FID C

　　---------- ---------- ----------

　　1 1 0

　　2 2 0

　　3 3 0

　　4 4 0

　　5 5 0

　　6 6 0

　　7 7 0

　　8 8 0

　　9 9 0

　　10 10 0

　　10 rows selected.

　　--删除过渡表了，收工。这时也可以考虑修改索引，约束触发器的名称与原来保持一致。南京Oracle认证培训

　　SQL> drop table inter_test cascade constraints purge;

　　Table dropped.