Sample script to drop interval partitions

SITUATION

The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts.

SQL> desc user_tab_partitions;

Name                    Null?         Type
----------------------- -------- --------------
TABLE_NAME                         VARCHAR2(30)
COMPOSITE                           VARCHAR2(3)
PARTITION_NAME                     VARCHAR2(30)
SUBPARTITION_COUNT                       NUMBER
HIGH_VALUE                                 LONG
HIGH_VALUE_LENGTH                        NUMBER
PARTITION_POSITION                       NUMBER

SOLUTION

Here is a basic sample script for dropping partitions more than 20 days old.

SQL> set serveroutput on
SQL> declare
     dt date;
     begin
       for x in (select table_name,partition_name, high_value 
                   from user_tab_partitions where INTERVAL='YES' )
       loop
        execute immediate 'select '||x.high_value||' from dual' into dt;
        if dt < sysdate - 20
        then
          dbms_output.put_line('to drop partition: '||x.table_name||'.'||x.partition_name);
          execute immediate 'alter table '||x.table_name||' drop partition '|| x.partition_name||' update indexes ';
        end if;
       end loop;
    end;
/

to drop partition: TBL_TEST1.SYS_P277
to drop partition: TBL_TEST1.SYS_P221
to drop partition: TBL_TEST2.SYS_P231
to drop partition: TBL_TEST2.SYS_P281
to drop partition: TBL_TEST3.SYS_P226
to drop partition: TBL_TEST3..SYS_P242

PL/SQL procedure successfully completed.