Convert a common table to a partitioned table by means of online redefinition

1. Create a common table (source table: li. p_table) createtableli. p_table (id_nnumber, date_ndate); 2. Insert data into the source table insertintoli. p_tablevalues (1, to_date (2013-01-0201:0, yyyy-mm-ddhh24: mi: ss); insertintoli. p_tablevaluees (2, to_date (2013-0

1. Create a common table (source table: li. p_table) create table li. p_table (id_n number, date_n date); 2. insert into li into the source table. p_table values (1, to_date (01:01:01, yyyy-mm-dd hh24: mi: ss); insert into li. p_table values (2, to_date (2013-0

1. Create a common table (source table: li. p_table)

Create table li. p_table (id_n number, date_n date );

2. Insert data to the source table

Insert into li. p_table values (1, to_date ('2017-01-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (2, to_date ('2017-02-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (3, to_date ('2017-03-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (4, to_date ('2017-04-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (5, to_date ('2017-05-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (6, to_date ('2017-06-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (7, to_date ('2017-07-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (8, to_date ('2017-08-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (9, to_date ('2017-09-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (10, to_date ('2017-10-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (11, to_date ('2017-11-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (12, to_date ('2017-12-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (13, to_date ('2017-01-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Insert into li. p_table values (14, to_date ('2017-02-02 01:01:01 ', 'yyyy-mm-dd hh24: mi: ss ')); Commit;

3. Create a temporary partition table

The structure of the temporary partition table must be consistent with that of the source table.

Create table li. p_partion_table (id_n number, date_n date) Partition by range (date_n) (PARTITION P_201301 values less than (TO_DATE ('2017-02-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201302 values less than (TO_DATE ('2017-03-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201303 values less than (TO_DATE ('2017-04-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201304 values less than (TO_DATE ('2017-05-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201305 values less than (TO_DATE ('2017-06-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201306 values less than (TO_DATE ('2017-07-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201307 values less than (TO_DATE ('2017-08-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201308 values less than (TO_DATE ('2017-09-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201309 values less than (TO_DATE ('2017-10-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201310 values less than (TO_DATE ('2017-11-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201311 values less than (TO_DATE ('2017-12-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201312 values less than (TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), PARTITION P_201401 values less than (TO_DATE ('2017-02-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')), Partition maxvalue values less than (MAXVALUE ));

4. Check whether the source table (li. p_table) can be redefined online (in the ROWID mode)

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (UNAME => 'lil', TNAME => 'P _ table', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID );

If the definition cannot be redefined online for some reason, an error message is returned.

(1) For tables without primary keys, when DBMS_REDEFINITION.CAN_REDEF_TABLE is used to check whether the table can be redefined online, specify the ROWID Method to Determine the unique row of data.

(2) For tables with primary keys, when DBMS_REDEFINITION.CAN_REDEF_TABLE is used to check whether the table can be redefined online, the unique row of data is determined using the cons_use_pk method.

5. Exchange data from the source table to the temporary table

EXEC DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => 'lil', ORIG_TABLE => 'P _ table', INT_TABLE => 'P _ partion_table ', OPTIONS_FLAG => Break );

6. Convert the source table to a partitioned table

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (UNAME => 'lil', ORIG_TABLE => 'P _ table', INT_TABLE => 'P _ partion_table ');

7. view the partition status of the source table (li. p_table)

Select table_owner, table_name, partition_name from dba_tab_partitions where table_name = 'P _ table' and table_owner = 'lil' The result is as follows: LI P_TABLE MAXVALUE LI P_TABLE P_201301 LI P_TABLE P_201302 LI P_TABLE P_201303 LI P_TABLE P_201304 LI P_TABLE P_201305 LI P_TABLE P_201306 LI P_TABLE P_201307 LI P_TABLE P_201308 LI P_TABLE P_201309 LI P_TABLE P_201310 LI P_TABLE P_201311 LI P_TABLE P_201312 LI P_TABLE P_201401

As described above, the source table li. p_table has been converted from a non-partition table to a time range partition table with monthly partitions.

8. view the data distribution of each partition in the source table (li. p_table ).

Select * from li. p_table partition (P_201301 ); Result: 1 2013/1/2 1:01:01 Select * from li. p_table partition (P_201303 ); Result: 3 2013/3/3 1:01:01

Note: The partition table has been redefined and all data is stored in partitions according to the partition range.

9. view the partition status of the temporary table (li. p_partion_table ).

Select table_owner, table_name, partition_name from dba_tab_partitions where table_name = 'P _ PARTION_TABLE 'and table_owner = 'lil' Result: null. Select owner, table_name, partitioned from dba_tables where table_name = 'P _ PARTION_TABLE 'and owner = 'lil '; Result: LI P_PARTION_TABLENO

The name of the created temporary partition table, which is already a non-partition table.

Note that the table under the SYS user cannot be redefined.

This article is the original "step-by-step". Please describe the source for forwarding!