Use ORACLE online redefinition to change a common table to a partitioned table and an oracle partitioned table

Use ORACLE online redefinition to change a common table to a partitioned table and an oracle partitioned table
1. First create a test table and insert the test data:

create table myPartition(id number,code varchar2(5),identifier varchar2(20));insert into myPartition values(1,'01','01-01-0001-000001');insert into myPartition values(2,'02','02-01-0001-000001');insert into myPartition values(3,'03','03-01-0001-000001');insert into myPartition values(4,'04','04-01-0001-000001');commit;alter table myPartition add constraint pk_test_id primary key (id);

2. Check whether the table can be redefined online. If no error is reported, the following error message is displayed:

-- Administrator privilege: Execute beginSQL> exec dbms_redefinition.can_redef_table ('Scott ', 'mypartition'); PL/SQL procedure successfully completed

-Run the end command as the administrator.

3. Create an intermediate table for online redefinition. The table structure is to define the original test table again. Here we create a partition table partitioned by the full number:

create table t_temp(id number,code varchar2(5),identifier varchar2(20)) partition by range(id)(            partition TAB_PARTOTION_01 values less than (2),            partition 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. Enable online redefinition:

-- Run SQL command line exec dbms_redefinition.start_redef_table ('Scott ', 'mypartition', 't_ temp') as the Administrator; -- run SQL command line as the Administrator

Here, the start_redef_table module of the dbms_redefinition package has three parameters: SCHEMA name, original table name, and intermediate table name.

5. After online redefinition is enabled, the data of the original table can be found in the intermediate table.

select * from t_temp;

6. Because data in the original data table may change during online redefinition in the generation system, inserting data into the original table to simulate data changes.

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

7. At this time, the original table is modified and the intermediate table is not updated.

select * from myPartition;select * from t_temp;

8. After you use the sync_interim_table module of the dbms_redefinition package to refresh the data, you can see the data changes in the intermediate table.

-- The Administrator has the permission to execute the SQL command line and synchronize the data exec dbms_redefinition.sync_interim_table ('Scott ', 'mypartition', 't_ temp') on both sides. -- the Administrator has the permission to execute the SQL command line.

Check whether the data on both sides is consistent after synchronization:

select * from myPartition;select * from t_temp;

9. End online redefinition

-- The Administrator has the permission to execute the SQL command line and end with redefinition of exec dbms_redefinition.finish_redef_table ('Scott ', 'mypartition', 't_ temp'); -- the Administrator has the permission to execute the SQL command line.

10. Verify data

select * from myPartition;select * from t_temp;

11. Check whether 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 online redefinition, the intermediate table is meaningless and the intermediate table is deleted.


drop table t_temp purge;


Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.