Oracle online redefinition of non-partition table to partition table conversion

Online conversion from non-partitioned tables to Partitioned Tables: SQL> select * from v $ version; BANNER implements 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: create the original and intermediate tables: SQL> create table testcol (a number, B varchar2 (20), d varchar2 (20 )); table created www.2cto.com SQL> create table testcol_tmp (a number, B varchar2 (20), d varchar2 (20) 2 partition by range () 3 (4 PARTITION test_part1 values less than (10), 5 PARTITION test_part2 values less than (20), 6 PARTITION test_part3 values less than (30 ), 7 PARTITION test_part4 values less than (40), 8 PARTITION test_part5 values less than (50) 9); Table created creates a primary key for the original Table based on the previous experience: SQL> alter table testcol add constraints pk_testcol primary key (a); Table altered insert data: insert into testcol values (1, '1', '1 '); insert into testcol values (11, '1', '1'); insert into testcol values (21, '1', '1'); insert into testcol values (31, '1', '1'); insert into testcol values (41, '1', '1'); SQL> select * from testcol; www.2cto.com a B D ---------- -------------------- 1 1 1 11 1 21 1 1 31 1 1 41 1
View the partitions of the original and intermediate tables before conversion: SQL> select table_name, partitioned from user_tables where table_name in ('testcol', 'testcol _ TMP '); TABLE_NAME PARTITIONED partition ----------- testcol no TESTCOL_TMP YES check whether the original table can be redefined online: SQL> exec partition ('Scott ', 'testcol'); PL/SQL procedure successfully completed check passed. Start Online redefinition: SQL> exec dbms_redefinition.start_redef_table ('Scott ', 'testcol', 'testcol _ TMP '); after PL/SQL procedure successfully completed is completed, the partition property has not changed. SQL> select table_name, partitioned from user_tables where table_name in ('testcol', 'testcol _ TMP '); TABLE_NAME PARTITIONED partition ----------- TESTCOL NO TESTCOL_TMP YES the primary key is not synchronized to the intermediate table either: SQL> select. constraint_name,. constraint_type from dba_constraints a where. table_name = 'testcol _ TMP '; www.2cto.com CONSTRAINT_NAME CONSTRAINT_TYPE ------------------------------ --------------- but the data has been transferred to the intermediate table: SQL> select * from testcol_tmp; a B D ---------- ------------------ 1 1 1 1 11 1 1 1 1 1 1 31 1 1 41 1 1sync_interim_table the purpose is to shorten the time for locking the table at finish: SQL> exec dbms_redefinition.sync_interim_table ('Scott ', 'testcol', 'testcol _ TMP '); PL/SQL procedure successfully completed completes the last step of online redefinition, this step will temporarily lock the original table: SQL> exec dbms_redefinition.finish_redef_table ('Scott ', 'testcol', 'testcol _ TMP '); after PL/SQL procedure successfully completed is completed, the primary key is switched over: SQL> select. constraint_name,. constraint_type from dba_constraints a where. table_name = 'testcol _ TMP '; CONSTRAINT_NAME CONSTRAINT_TYPE ---------------------------- ----------------- PK_TESTCOL PSQL> select. constraint_name,. constraint_type from dba_constraints a where. table_name = 'testcol'; CONSTRAINT_NAME CONSTRAINT_TYPE partition ------------- partition attributes are also exchanged: SQL> select table_name, partitioned from user_tables where table_name in ('testcol', 'testcol _ TMP '); www.2cto.com TABLE_NAME PARTITIONED partition ----------- testcol yes TESTCOL_TMP NO partition contains data: SQL> select * from testcol partition (test_part3); a B D ---------- partition ------------------ 21 1 Author