To change a normal table to a partitioned table using Oracle online redefinition

1. First build the test table and insert the test data:

 Create table mypartition (ID number, code VARCHAR2 (5), identifier VARCHAR2 ( ));  Insert  into mypartition values(1,' ',' 01-01-0001-000001 '); Insert  into mypartition values(2,'up ',' 02-01-0001-000001 '); Insert  into mypartition values(3,'',' 03-01-0001-000001 ' ); Insert  into mypartition values(4,' i ',' 04-01-0001-000001 '); commit; alter table mypartition add constraint pk_test_id primary key (ID);

2. Check whether this table can be redefined on-line, no error indication can be, errors will give the wrong message:

--管理员权限执行beginSQL> exec dbms_redefinition.can_redef_table(‘scott‘‘myPartition‘procedure successfully completed

– Administrator rights to execute end

3. Create an online redefinition of the required intermediate table, the table structure is to redefine the original test table to look like, here is established by the full number partition table:

create  table  t_ Temp (ID number , Code varchar2 (5 ), identifier VARCHAR2 (20 )) partition by  Range (ID) (Partit Ion tab_partotion_01 values  less than (2 ), p            Artition tab_partotion_02 values  less than (3 ),            Partition tab_partotion_03 values  less than (4 ), Partition tab_partotion_04 values  less than (5), partition Tab_partotion_other values  less THAN (MAXVALUE));  alter table t_temp add constraint pk_temp_id2 primary key ( ID);

4. Start the online redefinition:

--管理员权限执行sql命令行执行exec dbms_redefinition.start_redef_table(‘scott‘‘myPartition‘‘t_temp‘);--管理员权限执行sql命令行执行

Here the Start_redef_table module of the Dbms_redefinition package has 3 parameters, namely the schema name, the name of the original table, the name of the intermediate table.

5. After starting the online redefinition, the intermediate table can find the data of the original table.

select * from t_temp;

6. Because in the production system, the process of online redefinition of the data table may occur data changes, the original table inserted data simulation data changes.

insert into myPartition values(5,‘05‘,‘05-01-0001-000001‘);commit;

7. The original table was modified and the intermediate table was not updated.

select * from myPartition;select * from t_temp;

8. After you refresh the data using the Sync_interim_table module of the Dbms_redefinition package, the intermediate table can also see the data changes

--管理员权限执行sql命令行执行,同步两边数据exec dbms_redefinition.sync_interim_table(‘scott‘‘myPartition‘‘t_temp‘);--管理员权限执行sql命令行执行

Query whether the data on both sides of the synchronization is consistent:

select * from myPartition;select * from t_temp;

9. End the online redefinition

--管理员权限执行sql命令行执行，结束重定义exec dbms_redefinition.finish_redef_table(‘scott‘‘myPartition‘‘t_temp‘);--管理员权限执行sql命令行执行

10. Validating data

select * from myPartition;select * from t_temp;

11. See if the data in each partition is correct

select table_name, partition_name from user_tab_partitions where table_name = ‘myPartition‘;select * from myPartition partition(TAB_PARTOTION_01);

12. After the online redefinition, the intermediate table has no meaning, delete the intermediate table


drop table t_temp purge;


Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

To change a normal table to a partitioned table using Oracle online redefinition