Oracle的在线重定义表功能
在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。
如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。
举个在线重定义使用的例子：
SQL> create table test_primary (id number primary key);
表已创建。
SQL> insert into test_primary select rownum from dba_objects;
已创建6264行。
SQL> commit;
提交完成。
SQL> create table orgin_table (id number, fid number, name varchar2(30), create_date date)
? 2? tablespace users;
表已创建。
SQL> insert into orgin_table select rownum, rownum, object_name, created from dba_objects
? 2? where owner = 'SYS';
已创建4034行。
SQL> commit;
提交完成。
SQL> alter table orgin_table add constraint pk_orgin_table primary key (id);
表已更改。
SQL> create or replace trigger tr_orgin_table
? 2? before insert on orgin_table for each row
? 3? begin
? 4? null;
? 5? end;
? 6? /
触发器已创建
SQL> create index ind_orgin_table_create_date on orgin_table(create_date);
索引已创建。
SQL> alter table orgin_table add constraint f_orgin_primary_id foreign key (fid)
? 2? references test_primary (id);
表已更改。
SQL> begin
? 2? dbms_redefinition.can_redef_table('yangtk', 'orgin_table', dbms_redefinition.cons_use_pk);
? 3? end;
? 4? /
PL/SQL 过程已成功完成。
SQL> create table inter_table
? 2? (id number, fid number, new_name varchar2(30), create_date date, comments varchar2(1000))
? 3? tablespace yangtk
? 4? partition by range (create_date)
? 5? (partition p1 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
? 6? partition p2 values less than (maxvalue));
表已创建。
SQL> alter table inter_table add constraint pk_inter_table primary key (id);
表已更改。
SQL> begin
? 2? dbms_redefinition.start_redef_table('yangtk', 'orgin_table', 'inter_table',
? 3? 'ID ID, FID FID, NAME NEW_NAME, CREATE_DATE CREATE_DATE, ''NO COMMENTS'' COMMENTS',
? 4? DBMS_REDEFINITION.CONS_USE_PK);
? 5? END;
? 6? /
PL/SQL 过程已成功完成。
SQL> SELECT COUNT(*) FROM INTER_TABLE;
? COUNT(*)
----------
????? 4034
SQL> COL COMMENTS FORMAT A20
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> SELECT * FROM INTER_TABLE WHERE ID = 1;
??????? ID??????? FID NEW_NAME???? CREATE_DATE???????? COMMENTS
---------- ---------- ------------ ------------------- --------------------
???????? 1????????? 1 ACCESS$????? 2004-10-18 16:14:23 NO COMMENTS
SQL> INSERT INTO ORGIN_TABLE
? 2? SELECT 4034+ROWNUM, 4034+ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS
? 3? WHERE OWNER = 'SYSTEM';
已创建404行。
SQL> COMMIT;
提交完成。
SQL> begin
? 2? dbms_redefinition.sync_interim_table('yangtk', 'orgin_table', 'inter_table');
? 3? end;
? 4? /
PL/SQL 过程已成功完成。
SQL> select count(*) from INTER_TABLE;
? COUNT(*)
----------
????? 4438
SQL> alter table inter_table add constraint fk_inter_primary_id foreign key (fid)
? 2? references test_primary (id) disable;
表已更改。
SQL> create index ind_inter_table_date on inter_table(create_date);
索引已创建。
SQL> create index ind_inter_table_fid on inter_table(fid);
索引已创建。
SQL> create or replace trigger tr_inter_table
? 2? before insert on inter_table for each row
? 3? begin
? 4? null;
? 5? end;
? 6? /
触发器已创建
SQL> begin
? 2? dbms_redefinition.finish_redef_table('yangtk', 'orgin_table', 'inter_table');
? 3? end;
? 4? /
PL/SQL 过程已成功完成。
SQL> desc orgin_table
?名称??????????????????????????? 是否为空? 类型
?------------------------------- -------- ---------------
?ID????????????????????????????? NOT NULL NUMBER
?FID????????????????????????????????????? NUMBER
?NEW_NAME???????????????????????????????? VARCHAR2(30)
?CREATE_DATE????????????????????????????? DATE
?COMMENTS???????????????????????????????? VARCHAR2(1000)
SQL> select table_name, partition_name from user_tab_partitions where table_name = 'ORGIN_TABLE';
TABLE_NAME???????????????????? PARTITION_NAME
------------------------------ ------------------------------
ORGIN_TABLE??????????????????? P1
ORGIN_TABLE??????????????????? P2
SQL> select table_name, constraint_name, status from user_constraints
? 2? where table_name in ('ORGIN_TABLE', 'INTER_TABLE');
TABLE_NAME?????????? CONSTRAINT_NAME??????????? STATUS
-------------------- -------------------------- --------
INTER_TABLE????????? PK_ORGIN_TABLE???????????? ENABLED
INTER_TABLE????????? F_ORGIN_PRIMARY_ID???????? DISABLED
ORGIN_TABLE??????????PK_INTER_TABLE???????????? ENABLED
ORGIN_TABLE??????????FK_INTER_PRIMARY_ID??????? ENABLED
SQL> SELECT COUNT(*) FROM ORGIN_TABLE;
? COUNT(*)
----------
????? 4438
SQL> SELECT COUNT(*) FROM INTER_TABLE;
? COUNT(*)
----------
????? 4438
SQL> DROP TABLE INTER_TABLE;
表已丢弃。
?
