How to convert Partition table from RANGE to INTERVAL

This topic is pursue to previous tips by Burleson Team to show in steps how to convert a partition table from range to interval in order to avoid ORA-14400 error described before.

Let’s assume our partition table as below:

SQL> CREATE TABLE SALES

  2    (

  3   SALES_DATE DATE not null,

  4   SALES_DESC VARCHAR2(100)

  5    )

  6   PARTITION BY RANGE (SALES_DATE)

  7    (

  8   PARTITION P1  VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD') ),

  9   PARTITION P2  VALUES LESS THAN (TO_DATE('2012-07-01', 'YYYY-MM-DD') ),

 10   PARTITION P3  VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD') ),

 11   PARTITION PMAX VALUES LESS THAN (MAXVALUE));

Table created.

 The first step is to ensure that we don’t have MAXVALUE partition to avoid:

ORA-14759: SET INTERVAL is not legal on this table.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'SALES' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

PMAX            NO MAXVALUE

As described above that SALES table have a MAXVALUE partition and that would prevent the start of interval partitioning. Since this partition made to avoid outage insert, it should have zero row so we can drop it with no problem otherwise backup your data and truncate the partition and then drop it.

SQL> select count(*) from SALES partition (PMAX);

  COUNT(*)

----------

         0

SQL> alter table SALES drop partition PMAX;

Table altered.

In the next step we will convert the SALES table to interval and define the interval period of each partition.

Here I’m going to define 6 months interval:

SQL> alter table SALES set INTERVAL(NUMTOYMINTERVAL(6, 'MONTH'));

Table altered.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'ORDERS' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

Next, let’s test it by insert a row out of partitioned date range.

SQL> insert into SALES values(TO_DATE('2013-05-10', 'YYYY-MM-DD'),’DELL PC 930’ );

SQL> commit;

Commit complete.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'ORDERS' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P731        YES TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

The newly created partition has a system generated name and YES value for INTERVAL column and it has been allocated the right 6 months which is defined previously.

Finally, easily we can rename this system generated name to any name by:

SQL> alter table SALES rename partition SYS_P731 to P4;

Table altered.

SQL> select partition_name, interval, high_value from user_tab_partitions where table_name = 'ORDERS' order by partition_position;

PARTITION_NAME  IN HIGH_VALUE

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

----

P1              NO TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P2              NO TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P3              NO TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA

P4              YES TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:

                                  MI:SS', 'NLS_CALENDAR=GREGORIA