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

set_interval

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','dd.mm.yyyy')),
 partition q2 values less than (to_date('01.07.2016','dd.mm.yyyy')),
 partition q3 values less than (to_date('01.10.2016','dd.mm.yyyy')),
 partition q4 values less than (to_date('01.01.2017','dd.mm.yyyy'))
 );

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','dd.mm.yyyy'));
insert into sales_range values (4000001,'Hesse',1999,'Birmingham',to_date('27.01.2017','dd.mm.yyyy'))
            *
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','dd.mm.yyyy'));

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'; 

PARTITION_
----------
SYS_P249
Q4
Q3
Q2
Q1

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 by Rene Jeruschkat on December 1, 2016 - 10:38

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

    alter table sales_range set interval();

  2. #2 by Jens Vogel on December 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: