Oracle 根据日期自动分区定时删除分区数据

原创已于 2022-06-09 15:41:36 修改 · 1.8k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #sql

于 2022-06-09 15:36:14 首次发布

Oracle 同时被 2 个专栏收录

3 篇文章

订阅专栏

Database

3 篇文章

订阅专栏

本文介绍如何在Oracle 11G中使用INTERVALPARTITION按天分区，并通过存储过程自动删除超过30天的旧分区，以解决历史数据过大导致的磁盘空间问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

工作中遇到了这样的场景，一张历史表会保留每天的数据，但是随着时间的增加，这个历史表的数据会越来越大，导致磁盘空间占用越来越大，这个时候要求只保留最近30天的数据，该如何操作呢？

案例

Oracle 11G中INTERVAL PATITION可以按天分区，但是分区名是无规则的，没办法直接获取到想要的删除的分区名，这个时候可以查看一下数据库的字典表ALL_OBJECTS，利用存储过程来获取分区创建的时间，根据时间来删除分区。过程如下：

CREATE OR REPLACE PROCEDURE BI_COMPLIANCE_DEV2.PRO_AML_DM_COL_HIS_DEL(P_TABLE_ANME IN VARCHAR2) AS

  V_TABLE_NAME     VARCHAR2(100) := P_TABLE_ANME;
  V_PART_NAME      VARCHAR2(100);
  V_EXEC_SQL       VARCHAR2(500);

  CURSOR TABLE_PARTITION_LIST IS
    SELECT T.OBJECT_NAME    TABLE_NAME,
           T.SUBOBJECT_NAME PARTITION_NAME,
           T.CREATED        CREATE_DATE
      FROM USER_OBJECTS T
     WHERE  T.OBJECT_NAME = UPPER(V_TABLE_NAME)
       AND T.OBJECT_TYPE = 'TABLE PARTITION'
       AND T.GENERATED = 'Y'
       AND T.CREATED < SYSDATE - 30;

BEGIN

  FOR PART_NAME IN TABLE_PARTITION_LIST LOOP
    V_PART_NAME := PART_NAME.PARTITION_NAME;
    V_EXEC_SQL  := 'ALTER TABLE ' || V_TABLE_NAME || ' DROP PARTITION ' || V_PART_NAME;
  
    EXECUTE IMMEDIATE V_EXEC_SQL;
    COMMIT;
  END LOOP;
END;

这样就可以解决这个问题了