Home > Oracle > Interval partitioning with automated cleanup

Interval partitioning with automated cleanup

For large databases partitioning is not only great, it is necessary. Unfortunately it adds new problems. One such problem is how to create new partitions so that they are available when they are needed. It often requires the use of an overflow partition, moving data to the real partitions when they come online. It also tends to require priviliges not granted to developers.

With Oracle 11g this becomes much easier. With the new interval partitioning scheme the database automatically creates new partitions when they are needed. A template partition is created from the start; the system takes care of the rest.

For example, to create a new partition for each day:

create table test_tab (
  created_date   date default sysdate
 ,partition_key  date generated always as (trunc(created_date)) virtual
 ,test_col_1     number
) partition by range (partition_key)
    interval (NUMTODSINTERVAL(1,'DAY')) (
    partition test_tab_initial_part
      values less than (to_date('20100121', 'YYYYMMDD')

Note that a virtual column is used for the partitioning key; another 11g feature. Try it out:

insert into test_tab (test_col_1) values (1);
select partition_name, high_value
  from user_tab_partitions where table_name = 'TEST_TAB';

--------------------- ----------------------------
SYS_P42               TO_DATE(' 2010-10-29 ...

A new partition has been created on the fly.

While this is great it solves only half the problem. The old partitions must be dropped eventually. How can we automate that?

First we need to find the partitions that can be dropped. This is a bit tricky as the high_value column in user_tab_partitions is a LONG that contains a SQL expression rather than a value we can use in comparisons. Our first task is to write a function that converts a high_value to a date:

create or replace function get_high_value_as_date(
  p_table_name     in varchar2,
  p_partition_name in varchar2
) return date as
  v_high_value varchar2(1024);
  v_date        date;
  select high_value into v_high_value from user_tab_partitions
    where table_name = upper(p_table_name)
      and partition_name = upper(p_partition_name);
  execute immediate 'select ' || v_high_value || ' from dual' into v_date;
  return v_date;

Armed with the conversion function we can loop over the partitions for our table and drop the obsolete ones:

  c_days_to_keep constant integer := 31;
  x_last_partition exception;
  pragma exception_init(x_last_partition, -14758);
  for rec in (select table_name, partition_name
    from user_tab_partitions
      where table_name = 'TEST_TAB'
        and get_high_value_as_date(table_name, partition_name) <
        sysdate - c_days_to_keep) loop
      execute immediate 'alter table ' || rec.table_name || 
        ' drop partition ' || rec.partition_name;
      when x_last_partition then
  end loop;

Note that we cannot drop the template partition.

Categories: Oracle
  1. 2014-03-11 at 19:22

    This delete doesnot work

    • 2014-03-11 at 21:25

      The code works well enough in several production systems. If you provide more details perhaps I can help you. What does not work? Perhaps you don’t have Oracle 11g Enterprise Edition with the partitioning option?

  2. Eddie Gonzalez
    2016-09-15 at 06:40

    It works .. thank you.

  1. No trackbacks yet.

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: