Drop oldest partition automatically in oracle 11G

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

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.

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.

I too are interested if there is an automatic solution in 11g.
In older versions, I use one of two approaches:

1. Sticking to a strict name standard for partition names, for example SALE201101, SALE201102 (for january and february 2011), enables you to extract relevant data from ALL_TAB_PARTITIONS and then you can drop whatever partition is the oldest.

2. Suck it up and use a tiny metadata table with one column for partition_name and one correctly typed column for the time period (whether it's date, week, month, bimonthly, yearly, retail weeks). Then I select the oldest time period and drop the associated partition.

This isn't "full automatisch", but it makes automation easier.