Oracle online redefinition of modifying table structure

Online redefinition and modification of table structure: database version: SQL> select * from v $ version; BANNER Upgrade 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
Create the original table; 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 createdinsert 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 check whether the original table can be redefined online: SQL> exec partition ('Scott ', 'testcol'); begin dbms_redefinition.can_redef_table ('Scott ', 'testcol'); end; ORA-12089: The table without a primary key cannot be redefined online "SCOTT ". "TESTCOL" ORA-06512: In "SYS. DBMS_REDEFINITION ", line 137 ORA-06512: In" SYS. DBMS_REDEFINITION ", line 1478 ORA-06512: add a primary key SQL> alter table testcol add constr at line 2 www.2cto.com following the error prompt Aints pk_testcol primary key (a); Table altered: SQL> exec dbms_redefinition.can_redef_table ('Scott ', 'testcol'); PL/SQL procedure successfully completed passed. Start Online redefinition: SQL> exec dbms_redefinition.start_redef_table ('Scott ', 'testcol', 'testcol _ TMP ', 'a, B, d '); PL/SQL procedure successfully completed synchronize data: SQL> exec values ('Scott ', 'testcol', 'testcol _ TMP '); PL/SQL procedure successfully completed to complete online redefinition; 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 1 1 1 SQL> select * from testcol_tmp; a B D -------------------- 1 1 1 2 1 3 1 author Yafei Shi