【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
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
按照错误提示 添加主键
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
查看原始表和中间表的表结构和数据：
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