oracle 在线重组表

 

Oracle 9i后推出了DBMS_REDEFINITION软件包，这个软件包提供了在线重定义表的功能，可以在保持表可以更新的同时重新组织表的结构，内部原理是基于MV实现的，在线重定义表提供了很多好处：

 

#重组表数据，压缩空间

#在线把表重一个空间迁移到另外一个空间

#把表从普通表转换为分区表或者由分区表转换为普通表

#在线删除字段

#在线修改一些表参数，一些需要重组才生效的，如initrans.

 

 

---建立测试表

create table test (a int,b int);

---创建模拟数据

declare i integer;

begin

      for  i  in 1..100 loop

         insert into test values(i,100-i);

     end loop;

commit;

end;

/

 

---创建一个触发器，模拟表有触发器的情况

create table audit_test(id number);

create or replace trigger tr_test

 before insert or update or delete on test

 for each row

begin

 update audit_test set id=id+1;

 end;

/

 

     

 

在线重定义表的步骤：

#选择重定义的方式，有基于pk和基于rowid的方式，10G以上才支持ROWID的方式，10GR2以上才会自动unset m_row$$列

 

#检查表是否可以在线重定义(使用dbms_redifinition.can_redef_table过程）

 

SQL> exec dbms_redefinition.can_redef_table('TEST','TEST');

BEGIN dbms_redefinition.can_redef_table('TEST','TEST'); END;

 

*

ERROR at line 1:

ORA-12089: cannot online redefine table "TEST"."TEST" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479

ORA-06512: at line 1

提示没有PK，给test加上一个pk

 

SQL> alter table test add constraint pk_test_id primary key (a);

 

Table altered.

 

SQL> exec dbms_redefinition.can_redef_table('TEST','TEST');

 

PL/SQL procedure successfully completed.

 

可以发现可以重定义test了。

 

#创建需要重定义的中间表

SQL> create table int_test

  2  (a int,b int ,c int)

  3  partition by range(a)

  4  (partition p10 values less than (50),

  5   partition p20 values less than (100),

  6   partition p30 values less than (150),

  7   partition p40 values less than (200));

 

Table created.

 

 

 

#执行在线重定义：

---授予执行这个过程的权限:

create any table

alter any table

drop any table

lock any table

select any table

 

 

SQL> execute dbms_redefinition.start_redef_table('TEST','TEST','INT_TEST','a a,b b,0 c');

 

PL/SQL procedure successfully completed.

 

SQL>

 

# 在新建表上建立PK或则是唯一索引

 

SQL> alter table int_test add constraint pk_test_id1 primary key (a);

 

Table altered.

 

SQL>

 

 

 

#执行表同步，这步不是必须的，重构完成执行finish会自动调用，如果数据量很大，中间手动执行同步可以加快切换。

 

SQL> execute dbms_redefinition.sync_interim_table('TEST','TEST','INT_TEST');

 

PL/SQL procedure successfully completed.

 

SQL>

 

# 检查新表的权限，权限需要手工授予

#完成重构

execute dbms_redefinition.FINISH_REDEF_TABLE('TEST','TEST','INT_TEST');

 

 

注意：对于所有数据字典中的这两个表，只是完成了名字的对换，原表上的特性还需手工迁移过来，如索引，tirrger等。

 

 

最后的工作删除原表（原表现在为int_test 已经换名）。

 

 

 

 

 

 

 

 

 

 

 

 

 

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

转载于:http://blog.itpub.net/7314177/viewspace-590988/