Eliminando particiones viejas con un script

Digamos que hace tiempo creaste una tabla particionada para guardar muchos datos generados diariamente:

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'))
);

Estos datos se necesitan para generar algunos reportes pero después de algún tiempo no son necesarios, por lo que tienes que eliminar particiones viejas para no desperdiciar espacio en disco. Que tarea tan sencilla, no? Sólo tienes que ver información en USER_TAB_PARTITIONS y borrar las particiones más viejas:

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

Qué problema; hay muchas restricciones en la manipulación de datos tipo LONG por lo que no puedes sólo obtener la fecha de la columna HIGH_VALUE. Para esta tarea necesitarás un script PL/SQL:

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

Con este script puedes borrar particiones de dos días atrás y más antiguas, y ponerlo como un job diario para automatizar esta tarea.