Drop oldest partition automatically in oracle 11G

Question

I have a requirement to drop partition from an interval partitioned table, if the partition is older than three months.

Is there a oracle utility/function to do this? Or if not, how to implement this? Please guide me.

Database version: Oracle 11G

Answer 1

I don't know of any oracle utility or function to do this. You can find the information you need to write your own program to do this in the DBA_TAB_PARTITIONS or ALL_TAB_PARTITIONS views, similar to the following:

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE
  FROM SYS.DBA_TAB_PARTITIONS
  WHERE TABLE_OWNER = strSchema AND
        TABLE_NAME = strTable

where strSchema and strTable are the schema and table you're interested in. HIGH_VALUE is a LONG field which contains the code for a call to the TO_DATE function (assuming your table is partitioned on a date field); you'll need to assign HIGH_VALUE to a LONG field, then assign the LONG to a VARCHAR2 in order to get the value somewhere it can be manipulated, in a manner similar to:

lHigh_value     LONG;
strDate_clause  VARCHAR2(100);

lHigh_value := aRow.HIGH_VALUE;
strDate_clause := lHigh_value;

Then you just need to extract the appropriate fields from the DATE clause in order to determine which partitions you need to drop.

Share and enjoy.