Rename interval partitioning system generated partition names

Question

I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition.

PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY')) 
(PARTITION "p1_1"  VALUES LESS THAN (TO_DATE(' 01-jan-2013','DD-MON-YYYY' )))

This gives me a new system generated partition names every day (like SYS41, SYS42, ...).

I have two questions:

1. I want a script which should run daily and change this system generated name to custom name (like "01-jan-2013" and so on for every date). This script should be run daily through dbms_scheduler. I have a script which give me the name of latest created partition.

   SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS A
   WHERE PARTITION_POSITION = (
     SELECT MAX(PARTITION_POSITION) 
     FROM USER_TAB_PARTITIONS B
     WHERE A.TABLE_NAME = B.TABLE_NAME
       AND B.TABLE_NAME = 'IPART'
   );
   

   This may help you to give the answer.

2. I want a script which should delete the old partitions (say 3 months) automatically. This should also run daily. And that 3 months time should be configurable.

How can I achieve this?

Answer 1

There's a trick you can use, at least for date-range partitions: the HIGH_VALUE field in user_tab_partitions is a valid date expression. If you "eval" that, you'll get a date object for the cutoff point for that partition. You can then use that to build your partition name from.

Sample PL/SQL to try out (generates a string from the high_value column):

declare
  hv varchar2(9);
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO' and partition_name not like 'PART_%')
  loop
    execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
    dbms_output.put_line('alter table foo rename partition '||x.partition_name
                       ||' to PART_'||hv);
  end loop;
end;
/

(Replace the put_line with execute immediate once you've verified that it actually does what you want. The -1 in the date select is to make it more "human friendly", i.e. partition name to match the date it contains.)

As for dropping old partitions, you can use the same trick. Same type of loop to get a date object for each partition, and generate an alter table ... drop partition ... statement for the partitions you don't want to keep.

Simplistic example where you would drop partitions older than 10 days (be vary wary of off-by-one errors, there probably is one below):

declare
  dt date;
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO')
  loop
    execute immediate 'select '||x.high_value||' from dual' into dt;
    if dt < sysdate - 10 then
      dbms_output.put_line('to drop: '||x.partition_name);
    end if;
  end loop;
end;
/

You're then left with taking care of index maintenance if necessary.