How to drop multiple interval partitions based on date?

Solution 1:

You can use PL/SQL like this.

DECLARE
    CANNOT_DROP_LAST_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

   ts TIMESTAMP;
BEGIN
   FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'MY_TABLE') LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
      IF ts < SYSTIMESTAMP - INTERVAL '15' DAY THEN
      BEGIN
         EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE DROP PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
      EXCEPTION
            WHEN CANNOT_DROP_LAST_PARTITION THEN
                EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE SET INTERVAL ()';
                EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE DROP PARTITION '||aPart.PARTITION_NAME;
                EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE SET INTERVAL( INTERVAL ''1'' DAY )';            
      END;
      END IF;
   END LOOP;
END;

Solution 2:

For interval partitioned tables (that you probably use based on the exception ORA-14758) you may profit from using the partition_extended_name syntax.

You need not know the partition name, you reference the partition with a DATE, e.g.

alter table INT_PART drop partition for (DATE'2018-09-01')

So to drop your last 15 partitions starting with the current day this loop is to be performed:

declare
 v_sql VARCHAR2(4000);
begin
  for cur in (select  
                trunc(sysdate,'MM') - numtodsinterval(rownum - 1, 'day') my_month
              from dual connect by level <= 15) 
  loop
     v_sql := q'[alter table INT_PART drop partition for (DATE']'||
                 to_char(cur.my_month,'YYYY-MM-DD')||q'[')]';
     execute immediate v_sql;
  end loop;
end;
/

You must use execute immediateas the DATEin the ALTER TABLE statement must by static. Following statements are generated and executed:

alter table INT_PART drop partition for (DATE'2018-09-01')
alter table INT_PART drop partition for (DATE'2018-08-31')
....
alter table INT_PART drop partition for (DATE'2018-08-19')
alter table INT_PART drop partition for (DATE'2018-08-18')

Additional to the exception ORA-14758 (that I ignore - see the note below) you should handle the exception

ORA-02149: Specified partition does not exist

dependent on you business this should be probably ignored - for this day no partition exists (and you will never reference this day using the partition dictionary metadata).

Note to workaround the ORA-14758 Last partition in the range section cannot be dropped exception you may use this little trick.

I create a dummy partition (without an extent) called P_MINVALUE that plays the role of the interval start in the far past and it will therefore never be dropped.

CREATE TABLE int_part
      (
     transaction_date TIMESTAMP not null,
     vc_pad VARCHAR2(100)
      )
     SEGMENT CREATION DEFERRED 
     PARTITION BY RANGE (transaction_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
      (
     PARTITION P_MINVALUE  VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD') ) 
   );