在 11g 中维护按日间隔分区的最佳方法

Question

我在 11g 中创建了一个 sql 脚本，该脚本将删除数据库中所有分区表中所有值小于 60 天的高值分区

DECLARE
   TNAME      VARCHAR2 (300);
   PNAME      VARCHAR2 (300);
   HIGHVAL    VARCHAR2 (3000);
   POSITION   SMALLINT;
   VAL        LONG;
   CURSOR C1
   IS
      SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
        FROM USER_TAB_PARTITIONS
       WHERE TABLE_NAME NOT LIKE '%$%'
         AND TABLE_NAME NOT LIKE 'BIN%';
BEGIN
   OPEN C1;
   LOOP
      FETCH C1
       INTO TNAME, PNAME, POSITION, VAL;
      HIGHVAL := VAL;
      EXIT WHEN C1%NOTFOUND;
      IF TO_DATE (SUBSTR (HIGHVAL, 10, 11), 'RRRR-MM-DD') <
                                                          TRUNC (SYSDATE)
                                                          - 60
      THEN
         IF POSITION = 1
         THEN
            DBMS_OUTPUT.PUT_LINE ('ALTER TABLE ' || TNAME
                                  || ' SET INTERVAL();'
                                 );
         END IF;
         DBMS_OUTPUT.PUT_LINE (   'ALTER TABLE '
                               || TNAME
                               || ' DROP PARTITION '
                               || PNAME
                               || ' UPDATE GLOBAL INDEXES PARALLEL 2;'||CHR(10)
                               || '--DROPPED'
                               || '--'
                               || TO_DATE (SUBSTR (HIGHVAL, 10, 11),
                                           'RRRR-MM-DD'
                                          )
                              );
         IF POSITION = 1
         THEN
            DBMS_OUTPUT.PUT_LINE
                                (   'ALTER TABLE '
                                 || TNAME
                                 || ' SET INTERVAL(NUMTODSINTERVAL(1,''DAY''));'
                                );
         END IF;
      END IF;
   END LOOP;
   COMMIT;
   CLOSE C1;
END;
/

我正在执行生成的 SQL 文本

如果这是正确的，请告知任何改进的余地???

Answer 1

一些提示：

• 这是隐式光标的理想位置，因此您无需手动声明变量，获取，打开和关闭，...
• 在查询中筛选高值，越早越好
• 使用"立即执行"应用更改，而不是手动执行打印的任何内容
• 你不需要提交，因为它都是DDL

代码：

BEGIN
  FOR p IN (SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
            FROM USER_TAB_PARTITIONS
            WHERE TABLE_NAME NOT LIKE '%$%'
            AND TABLE_NAME NOT LIKE 'BIN%'
            AND TO_DATE (SUBSTR (HIGH_VALUE, 10, 11), 'RRRR-MM-DD') < TRUNC (SYSDATE) - 60)
  LOOP
    IF p.PARTITION_POSITION = 1
    THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || p.TABLE_NAME || ' SET INTERVAL()';
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE ' || p.TABLE_NAME
                   || 'DROP PARTITION ' || p.PARTITION_NAME
                   || ' UPDATE GLOBAL INDEXES PARALLEL 2';
    IF p.PARTITION_POSITION = 1
    THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || p.TABLE_NAME
                     || ' SET INTERVAL(NUMTODSINTERVAL(1,''DAY''));';
    END IF;
  END LOOP;
END;
/

还有一些警告：

注意更新全局索引，它不适用于物联网表（如果您使用它们）（我错了）
• 注意重置/设置间隔。一般规则是不能删除最后一个非间隔分区。猜测这将是位置 1 是有风险的。最好依靠user_tab_partitions.interval标志。