How to change RANGE- to INTERVAL-Partitioning in #Oracle


An existing RANGE partitioned table can easily be changed to be INTERVAL partitioned with the SET INTERVAL command. My table has been created initially like this:

SQL> create table sales_range (id number, name varchar2(20),
 amount_sold number, shop varchar2(20), time_id date)
 partition by range (time_id)
 partition q1 values less than (to_date('01.04.2016','')),
 partition q2 values less than (to_date('01.07.2016','')),
 partition q3 values less than (to_date('01.10.2016','')),
 partition q4 values less than (to_date('01.01.2017',''))

That way, an insert that falls out of the last existing range fails:

SQL> insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017',''));
insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017',''))
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Now instead of having to add a new range each time, the table can be changed to add that new range automatically if new rows require them. In other words, the 11g Feature Interval Partitioning can be added after the initial creation of the table:

SQL> alter table sales_range set interval(numtoYMinterval(3,'MONTH'));

Table altered.

SQL> insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017',''));

1 row created.

SQL> commit;

Commit complete.

SQL> col partition_name for a10
SQL> select partition_name from user_tab_partitions where table_name='SALES_RANGE'; 


This is documented, of course. But the ALTER TABLE command is probably one of the most voluminous, so good luck in working your way through it until you find that part 😉


  1. #1 von Rene Jeruschkat am Dezember 1, 2016 - 10:38

    Thanks, I immediately asked myself how you change back and found this:

    alter table sales_range set interval();

  2. #2 von Jens Vogel am Dezember 12, 2016 - 16:41

    How does it work for partitioned tables using MAXVALUE partition?

  1. Log Buffer #496: A Carnival of the Vanities for DBAs | Official Pythian® Blog – Cloud Data Architect

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..