4

I have a table based on daily partitions.

I can drop a paritition using the below query

ALTER TABLE MY_TABLE DROP PARTITION FOR(TO_DATE('19-DEC-2017','dd-MON-yyyy'))

How can I drop all the partitions (multiple partitions) before 15 days?

0xdb
  • 3,539
  • 1
  • 21
  • 37
TomJava
  • 499
  • 1
  • 8
  • 24
  • Possible duplicate of [Oracle : Drop multiple partitions](https://stackoverflow.com/questions/32110948/oracle-drop-multiple-partitions) – XING Jan 04 '18 at 05:54
  • I understand the delete based on name. I am trying to check whether any alternatives based on date range. – TomJava Jan 04 '18 at 06:09
  • 1
    You can drop partition on name as well. `ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008, sales_q3_2008, sales_q4_2008;` . I dont thing there is something on date range – XING Jan 04 '18 at 06:13
  • @XING - THANK YOU – TomJava Jan 04 '18 at 06:28

2 Answers2

8

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;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • In the table USER_TAB_PARTITIONS, high value is having "TIMESTAMP' 2017-01-01 00:00:00'" . So any changes I will have to make? – TomJava Jan 04 '18 at 06:41
  • Why is this 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' required? Why we cannot directly use aPart.HIGH_VALUE? – TomJava Jan 04 '18 at 07:30
  • 1
    Because `HIGH_VALUE` is data type `LONG` which has many limitations. You cannot use it directly, `IF aPart.HIGH_VALUE < ...` or `FROM USER_TAB_PARTITIONS WHERE HIGH_VALUE < ...` will fail. – Wernfried Domscheit Jan 04 '18 at 08:36
  • how can we prevent getting the exception "Last partition in the range section cannot be dropped" – TomJava Jan 04 '18 at 08:48
  • 1
    I put it in my answer. The last (i.e. the initaly created) partition you have to drop manually. Once the last partition is dropped you will not get the error again. – Wernfried Domscheit Jan 04 '18 at 08:54
  • can we handle this "ORA-14758"? Any named exception? I am planning to keep it in exception block – TomJava Jan 04 '18 at 08:55
  • :) Sorry for that. But ur answer is extremely informative. – TomJava Jan 04 '18 at 09:09
  • 1
    Regarding "Why we cannot directly use aPart.HIGH_VALUE?", it is because this column is stored as a LONG in the database. It is hence a string representation of that timestamp, not the timestamp itself. – Connor McDonald Jan 05 '18 at 05:51
3

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') ) 
   );
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53