在高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线重定义表功能,通过调用DBMS_REDEFINITION包,可以在修改表结构的同时允许DML操作。
在线重定义表具有以下功能:
修改表的存储参数;
可以将表转移到其他表空间;
增加并行查询选项;
增加或删除分区;
重建表以减少碎片;
将堆表改为索引组织表或相反的操作;
增加或删除一个列。
注意:调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色,除此之外,还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
利用oracle的在线重定义功能:
SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, TIME DATE);
表已创建。
SQL> INSERT INTO T1 SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
已创建6264行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('USER', 'T1', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 过程已成功完成。
SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2015-01-01', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE('20016-01-01', 'YYYY-MM-DD')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE('20017-01-01', 'YYYY-MM-DD')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE));
表已创建。
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(‘USER’, 'T1', 'T_NEW', 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);
可以简写为:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(‘USER’, 'T1', 'T_NEW')
PL/SQL 过程已成功完成。
SQL> EXEC dbms_redefinition.sync_interim_table(‘USER’, 'T1', 'T_NEW')
现在,将中间表与原始表同步。(仅当要对表 T 1进行更新时才需要执行该操作。)
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘USER’, 'T1', 'T_NEW');
PL/SQL 过程已成功完成。
在线重定义转换分区表成功。