oracle联机重定义修改表结构
联机重定义修改表结构：
数据库版本：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production www.2cto.com
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
创建原始表；
SQL> create table testcol (a varchar2(20),b varchar2(20),d varchar2(20));
Table created
SQL> create table testcol_tmp (a varchar2(20),b varchar2(20),c varchar2(20),d varchar2(20));
Table created
insert into testcol values('1','1','1');
insert into testcol values('2','1','1');
insert into testcol values('3','1','1');
SQL> select * from testcol;
A B D
-------------------- -------------------- --------------------
1 1 1
2 1 1
3 1 1
检测原始表是否能够联机重定义：
SQL> exec dbms_redefinition.can_redef_table('SCOTT','TESTCOL');
begin dbms_redefinition.can_redef_table('SCOTT','TESTCOL'); end;
ORA-12089: 不能联机重新定义无主键的表 "SCOTT"."TESTCOL"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 2 www.2cto.com
按照错误提示 添加主键
SQL> alter table testcol add constraints pk_testcol primary key (a);
Table altered
再次检测：
SQL> exec dbms_redefinition.can_redef_table('SCOTT','TESTCOL');
PL/SQL procedure successfully completed
通过。
开始联机重定义：
SQL> exec dbms_redefinition.start_redef_table('SCOTT','TESTCOL','TESTCOL_TMP','a a,b b,d d');
PL/SQL procedure successfully completed
同步数据：
SQL> exec dbms_redefinition.sync_interim_table('SCOTT','TESTCOL','TESTCOL_TMP');
PL/SQL procedure successfully completed
完成联机重定义;
SQL> exec dbms_redefinition.finish_redef_table('SCOTT','TESTCOL','TESTCOL_TMP');
PL/SQL procedure successfully completed
查看原始表和中间表的表结构和数据： www.2cto.com
SQL> select * from testcol;
A B C D
-------------------- -------------------- -------------------- --------------------
1 1 1
2 1 1
3 1 1
SQL> select * from testcol_tmp;
A B D
-------------------- -------------------- --------------------
1 1 1
2 1 1
3 1 1
作者 雅飞士
oracle联机重定义修改表结构 
