Convert Range partition table into Interval Partitioning table in 11g

In this Post I will Teach you How to convert Range partition table into Interval partition table.


Step 1 ) Create one Range Partition Table EMP_INFO


CREATE TABLE emp_info
(
emp_id NUMBER NOT NULL,
join_date DATE NOT NULL,
email VARCHAR2(100)
)
PARTITION BY RANGE (join_date)
(
PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
 PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
 PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY'))
 );

Note : Above table Yearly Range Partitioned.We have created a table EMP_INFO with five partitions to hold records for the year 2010, 2011, 2012, 2013 and 2014 on separate partitions.

Step 2) Insert Dummy Data into above Partitions 

Now, Lets load some data into the table

declare

 begin

 for i in 1..100000

 loop

 insert into emp_info values (i,'13-APR-2010','xyz'||i);

 end loop;

 end;

 /

declare

 begin

 for i in 100001..200000

 loop

 insert into emp_info values (i,'13-APR-2011','xyz'||i);

 end loop;

 end;

 /

declare

 begin

 for i in 200001..300000

 loop

 insert into emp_info values (i,'13-APR-2012','xyz'||i);

 end loop;

 end;

 /

declare

 begin

 for i in 300001..400000

 loop

 insert into emp_info values (i,'13-APR-2013','xyz'||i);

 end loop;

 end;

 /

declare

 begin

 for i in 400001..500000

 loop

 insert into emp_info values (i,'13-APR-2014','xyz'||i);

 end loop;

 end;

 /

commit;

declare

 begin

 for i in 500001..600000

 loop

 insert into emp_info values (i,'13-APR-2015','xyz'||i);   =======> will fail 

 end loop;

 end;

 /

above statement will fail due to it will not come under pre defined range partitions. 

select count(*) from emp_info;

Step 3) Gather Stats on Table and  Make sure how much count number of rows in each partitions 

execute dbms_stats.gather_table_stats(ownname => 'XXXXXXXXXXXX', tabname => 'EMP_INFO', estimate_percent=> 100, degree=> 16, cascade => TRUE);

SQL>  select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS

------------------------------ ------------------------------ ----------

EMP_INFO                       EMP_INFO_P0                        100000

EMP_INFO                       EMP_INFO_P1                        100000

EMP_INFO                       EMP_INFO_P2                        100000

EMP_INFO                       EMP_INFO_P3                        100000

EMP_INFO                       EMP_INFO_P4                        100000

Step 4) Now Convert to Interval partition by executing below command 

SQL>  alter table EMP_INFO SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR'));

Table altered.

Step 5) Now load Data Again into table 

declare

 begin

 for i in 500001..600000

 loop

 insert into emp_info values (i,'13-APR-2015','xyz'||i);

 end loop;

 end;

 /

declare

 begin

 for i in 600001..700000

 loop

 insert into emp_info values (i,'13-APR-2016','xyz'||i);

 end loop;

 end;

 /

declare

 begin

 for i in 700001..800000

 loop

 insert into emp_info values (i,'13-APR-2017','xyz'||i);

 end loop;

 end;

 /

Step 6) Gather stats on Partition Table

 execute dbms_stats.gather_table_stats(ownname => 'SNANABALA', tabname => 'EMP_INFO', estimate_percent=> 100, degree=> 16, cascade => TRUE);

Step 7 ) Verify Interval paritions are Created or not 

Its should created interval partitions since we inserted the data in Step 5 which is next range of data. Interval paritions will created by system defined names usually it will start with SYS_PXXX . lets verify  by executing below command. 

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS

------------------------------ ------------------------------ ----------

EMP_INFO                       EMP_INFO_P0                        100000

EMP_INFO                       EMP_INFO_P1                        100000

EMP_INFO                       EMP_INFO_P2                        100000

EMP_INFO                       EMP_INFO_P3                        100000

EMP_INFO                       EMP_INFO_P4                        100000

EMP_INFO                       SYS_P127                           100000

EMP_INFO                       SYS_P128                           100000

EMP_INFO                       SYS_P129                           100000

8 rows selected.

Its created Interval partitions. 

Happy interval partitioning.  

Note: Above exercise is purely based on my testing .  Please test it before moving to critical systems.