Dropping old partitions with a script

Lets say that you created a partitioned table some time ago to hold a lot of data generated by day:

CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

This data is needed to generate some reports, but after some time it is not necessary anymore so you have to drop old partitions in order to not waste disk space. What a simple task, isn't it? You just have to look for information in USER_TAB_PARTITIONS and drop the oldest partitions:

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P193448                    TO_DATE(' 2011-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193449                    TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193450                    TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE;
select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE
                       *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';
select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0'
                              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

What a problem; there is a lot of restrictions manipulating LONG datatypes so you cannot just get the date from the HIGH_VALUE column. For this task you'll need a PL/SQL script:

SQL> DECLARE
  v_sql             varchar2(500);
  v_date            date;
  v_partition_name  user_tab_partitions.partition_name%TYPE;
  v_high_value      user_tab_partitions.high_value%TYPE;
  CURSOR c1 IS select PARTITION_NAME, HIGH_VALUE
               from user_tab_partitions where table_name='MY_TABLE' and PARTITION_NAME!='P0';
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_partition_name, v_high_value;
    EXIT WHEN c1%NOTFOUND;
    v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
    IF v_date < (sysdate - 2) then
      v_sql := 'alter table MY_TABLE drop partition ' || v_partition_name;
      execute immediate v_sql;
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

PL/SQL procedure successfully completed.

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P193449                    TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193450                    TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

With this script you can drop partitions from 2 days ago and older, and put it as a daily job in order to automate this task.