ITPub博客

首页 > 数据库 > Oracle > Oracle在线重定义

Oracle在线重定义

Oracle 作者:abraham_dba_2013 时间:2015-03-23 09:37:21 0 删除 编辑
在线重定义[@more@]Oracle9i之前,表数据的整理是通过 alter table XXX move [tablespace XX]进行的。如果表非常大,IO又不快的时候,move的过程可能相当漫长,不能算是HA特性。因此在HA的在线维护中,基本不会利用move来重组大型表,而且move后的表需要重建索引。而在9i引入的dbms_redefinition。该过程的内部原理其实就是采用了MV的机制,类似在原表建立一个prebuilt的MV然后一直增量刷新到数据差别最小。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.

--添加主键约束以及外键约束,并在外键上建立索引

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是否可以在线重定义。一般情况没有主键等会报错。
SQL> begin
2 dbms_redefinition.can_redef_table('BOYLOOK','TEST');
3 end;
4 /

PL/SQL procedure successfully completed.

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

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

--执行表同步
注:这一步不是必须的,但是对于比较大的表,中间运行增量同步有助于减少切换时间。

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赋予同样的权限。否则原表的权限不会转移到新表

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的换名工作,只是所有的约束,索引或触发器名称还是保持着原来表上面的名称

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');

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.

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

SQL> drop table inter_test cascade constraints purge;

Table dropped.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29439655/viewspace-1469934/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-13

  • 博文量
    90
  • 访问量
    395764