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

(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.


    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?

  • 1
    Daily partitions? How many billion rows are you dealing with? – Philᵀᴹ Jan 20 '13 at 14:35
  • Hi Phil, I am not sure how many rows are there in the client side. But, It will cross billion for sure. I wanted a script which will run every 24 hours and rename the newly created system partition because of interval partitioning. And another script which will delete 3 months old partitions daily. This will be running every 24 hours. I also have some global indexes. – Rajesh Jan 21 '13 at 6:25

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):

  hv varchar2(9);
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO' and partition_name not like 'PART_%')
    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;

(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):

  dt date;
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO')
    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;

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

  • Thank you Mat for your answer. I just have one doubt in the renaming part. Is that a daily script. Or, that is like you can run once in a month, and it will change all the system generated partition names. – Rajesh Jan 21 '13 at 5:02
  • Look at the last part of the where clause. It renames all partitions that don't match the scheme. You could change that so that it renames all partitions that match the system-generated scheme. (You could run that every hour if you fell like it, or every year.) – Mat Jan 21 '13 at 6:09
  • One more query, going through the whole table will cause any performance issue as my table is huge and is live(insertions keep happening all the time) even when you will be partitioning the table. I have some global indexes, will that cause a problem? – Rajesh Jan 21 '13 at 6:27
  • Dropping partitions without the appropriate update indexes clause will invalidate all global indexes. That's potentially a big problem if your table is huge. It might actually prevent you from dropping them during normal operations and restrict you to week-ends (if that's acceptable). (The rename part doesn't touch the table data and doesn't invalidate indexes.) – Mat Jan 21 '13 at 6:34
  • Can you give me script to update indexes while dropping the partitions. Unfortunately my table is huge and this drop partition script should run every 24 hours and the table is live as well. What can be done for this?? – Rajesh Jan 22 '13 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.