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

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

Solution 2

This is wonky and inelegant, but it does work for casting the VALUES LESS THAN some_date expression in DBA_TAB_PARTITIONS, at least for range partitioning, including interval partitioning, in 10g and 11g. Inspired by Tom Kyte's "Evaluate Expression" question. Your mileage may vary.

declare
  l_hival     varchar2(4000);
  l_sql       varchar2(4000);
  l_high_date date;
  l_cursor    integer default dbms_sql.open_cursor;
  l_rows_back number;
begin
  -- partition position = 1 always grabs the "oldest" partition
  select high_value
    into l_hival
    from dba_tab_partitions
   where table_name = <my_range_partitioned_table>
     and partition_position = 1;

  dbms_sql.parse (l_cursor, 
                  'begin :retval := ' || l_hival || '; end;',
                  dbms_sql.native);

  dbms_sql.bind_variable (l_cursor, ':retval', l_high_date);
  l_rows_back := dbms_sql.execute (l_cursor);
  dbms_sql.variable_value (l_cursor, ':retval', l_high_date);
  dbms_output.put_line (to_char(l_high_date, 'yyyy-mm-dd-hh24.mi.ss'));
end;
/

As it's PL/SQL, it could be encapsulated into a function to return the "high value" for any partitioned table passed in as arguments.

Comments

[William Robertson]

There is no auto-partition-purge utility provided. You'll need to write one.