Oracle分区表的一些简单技巧

1.查找表分区的情况

 SELECT *  FROM USER_TAB_PARTITIONS
  WHERE TABLE_NAME = XXXX

可查对应表的具体分区情况

2.分区表清空指定分区

不删分区仅清空分区数据推荐使用

ALTER TABLE TABLE_NAME TRUNCATE PARTITION_NAME

不建议使用

DELETE FROM TABLE_NAME WHERE PARTITION_NAME = XXXX

DELETE FROM 的形式对于数据量大的表来说有可能导致数据库服务卡死，且对水位线无影响

清空数据且删除数据

ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME

3.结合以上两点的一个小的实际应用

目标表为list分区，且存储周期为36个月，每个存储周期为1个分区，分区名为SUM_MONTHYYYYMM，逻辑实现表同步之前的分区清空操作

 

具体代码如下

DECLARE
  V_SQL       VARCHAR2(500);
  V_SUM_MONTH VARCHAR2(10) := '201910';
  V_PART_RES  NUMBER :=0;
  V_PART_NUM  NUMBER :=0;
  V_MIN_MONTH VARCHAR2(30);
BEGIN
  V_SQL := 'SELECT COUNT(1) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TABLE_NAME'' AND PARTITION_NAME= ''SUM_MONTH' || V_SUM_MONTH || '''';
  EXECUTE IMMEDIATE V_SQL
    INTO V_PART_RES;
  --DBMS_OUTPUT.PUT_LINE(V_PART_RES);
  IF V_PART_RES = 0 THEN --判断是否有对应分区
    BEGIN --没有分区则添加对应分区
      V_SQL := 'SELECT COUNT(1) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TABLE_NAME''';
      EXECUTE IMMEDIATE V_SQL  INTO V_PART_NUM; --查询当前存储周期数
      IF V_PART_NUM >= 36 THEN --超过36个周期清除最小周期
        BEGIN
          V_SQL := 'SELECT MIN(PARTITION_NAME) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TABLE_NAME''';
          EXECUTE IMMEDIATE V_SQL  INTO V_MIN_MONTH; --查询当前最小统计周期
          EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME DROP PARTITION ' ||V_MIN_MONTH; --删除最早的分区
        END;
      END IF;
      V_SQL := 'ALTER TABLE TABLE_NAME ADD PARTITION SUM_MONTH' ||
               V_SUM_MONTH || ' VALUES(''' || V_SUM_MONTH || ''')';
      EXECUTE IMMEDIATE V_SQL;
    END;
  ELSE
    BEGIN --有分区则清空对应分区
      EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME TRUNCATE PARTITION SUM_MONTH'||V_SUM_MONTH;
    END;
  END IF;
END;

需要注意，分区名称格式得固定，且需要同步的分区字段格式不变
如果有其他更好的实现方法欢迎讨论