根据日期删除多个分区 [英] Drop multiple partitions based on date

问题描述

我有一个基于每日分区的表.

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

如何在15天之前删除所有分区(多个分区)?

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

推荐答案

您可以像这样使用PL/SQL.

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;

这篇关于根据日期删除多个分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！