Oracle 11g自动添加分区的存储过程优化与调度配置指导

背景 #

基于Oracle 11g搭建新数据仓库（DWH）系统，通过以下SQL定位包含2023年度分区的非系统用户表，发现大量表未使用interval分区：

SELECT DISTINCT(a.table_name)  
FROM dba_TAB_PARTITIONS a 
WHERE table_owner NOT IN (SELECT name 
                          FROM system.logstdby$skip_support 
                          WHERE action = 0) 
  AND partition_name LIKE '%2023%';

需求：实现一个存储过程，自动检查非系统用户的分区表，识别其分区粒度（小时/日/月）、分区列及所属表空间，自动创建缺失分区；同时配置调度任务实现自动执行。

当前编写的存储过程无法适配不同分区粒度，代码如下：

CREATE OR REPLACE PROCEDURE ADD_MISSING_PARTITIONS_FOR_ALL_TABLES AS
    v_table_owner VARCHAR2(30);
    v_table_name  VARCHAR2(30);
    v_partition_col VARCHAR2(30) := 'DATE_COLUMN'; -- 
    v_next_partition_date DATE;
    v_last_partition_name VARCHAR2(30);
    v_last_tablespace VARCHAR2(30);
BEGIN
    FOR table_rec IN (SELECT owner, table_name
                      FROM all_tables
                      WHERE owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'SYSMAN')) LOOP
        v_table_owner := table_rec.owner;
        v_table_name := table_rec.table_name;

        SELECT MAX(partition_name), tablespace_name
        INTO v_last_partition_name, v_last_tablespace
        FROM all_tab_partitions
        WHERE table_name = v_table_name AND table_owner = v_table_owner;

        FOR i IN 1..12 LOOP
            v_next_partition_date := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), i);

            DECLARE
                v_new_partition_name VARCHAR2(30);
                v_new_tablespace VARCHAR2(30);
            BEGIN
                v_new_partition_name := 'P'|| TO_CHAR(v_next_partition_date, 'YYYYMM');
                v_new_tablespace := v_last_tablespace; -- Son tablespace'ten alıyoruz
            END;

            IF NOT EXISTS(SELECT 1
                          FROM all_tab_partitions
                          WHERE table_name = v_table_name
                          AND table_owner = v_table_owner
                          AND partition_name = v_new_partition_name) THEN
                -- Yeni partition'ı ekliyoruz
                EXECUTE IMMEDIATE 'ALTER TABLE ' || v_table_owner || '.' || v_table_name ||
                                  ' ADD PARTITION ' || v_new_partition_name ||
                                  ' VALUES LESS THAN (''' || TO_CHAR(v_next_partition_date, 'YYYY-MM-DD') || ''') TABLESPACE ' || v_new_tablespace;
                DBMS_OUTPUT.PUT_LINE('Partition added for ' || v_table_owner || '.' || v_table_name || ': ' || v_new_partition_name);
            END IF;
        END LOOP;
    END FOR;
END ADD_MISSING_PARTITIONS_FOR_ALL_TABLES;
/

完善后的存储过程 #

以下存储过程可自动识别分区表的粒度、分区列，根据现有分区规则生成缺失分区：

CREATE OR REPLACE PROCEDURE ADD_MISSING_PARTITIONS_FOR_ALL_TABLES AS
    -- 定义变量存储分区表元数据
    v_table_owner          VARCHAR2(30);
    v_table_name           VARCHAR2(30);
    v_partition_col        VARCHAR2(30);
    v_interval_unit        VARCHAR2(10); -- HOUR/DAY/MONTH
    v_last_part_high_val   DATE;
    v_last_tablespace      VARCHAR2(30);
    v_new_part_name        VARCHAR2(30);
    v_new_part_high_val    DATE;
    v_create_sql           VARCHAR2(1000);
    v_num_part_to_create   NUMBER := 30; -- 预创建未来30个粒度单位的分区，可调整
BEGIN
    -- 遍历所有非系统用户的RANGE分区表
    FOR tbl_rec IN (
        SELECT distinct t.owner, t.table_name
        FROM all_tables t
        JOIN all_part_tables pt ON t.owner = pt.owner AND t.table_name = pt.table_name
        WHERE t.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'SYSMAN', 'SYSAUX')
          AND pt.partitioning_type = 'RANGE'
          AND pt.interval IS NULL -- 只处理非interval分区表
    ) LOOP
        v_table_owner := tbl_rec.owner;
        v_table_name := tbl_rec.table_name;

        -- 获取分区列信息
        SELECT column_name
        INTO v_partition_col
        FROM all_part_key_columns
        WHERE owner = v_table_owner AND name = v_table_name AND object_type = 'TABLE';

        -- 获取最后一个分区的边界值和表空间
        SELECT high_value, tablespace_name
        INTO v_last_part_high_val, v_last_tablespace
        FROM (
            SELECT 
                TO_DATE(
                    REGEXP_SUBSTR(high_value, '''(.*?)''', 1, 1, NULL, 1),
                    'YYYY-MM-DD HH24:MI:SS'
                ) AS high_value,
                tablespace_name
            FROM all_tab_partitions
            WHERE owner = v_table_owner AND table_name = v_table_name
            ORDER BY partition_position DESC
        )
        WHERE ROWNUM = 1;

        -- 识别分区粒度：通过最后两个分区的边界差判断
        DECLARE
            v_prev_part_high_val DATE;
            v_interval_days      NUMBER;
        BEGIN
            SELECT TO_DATE(
                       REGEXP_SUBSTR(high_value, '''(.*?)''', 1, 1, NULL, 1),
                       'YYYY-MM-DD HH24:MI:SS'
                   )
            INTO v_prev_part_high_val
            FROM (
                SELECT high_value
                FROM all_tab_partitions
                WHERE owner = v_table_owner AND table_name = v_table_name
                ORDER BY partition_position DESC
            )
            WHERE ROWNUM = 1 OFFSET 1 ROW;

            v_interval_days := v_last_part_high_val - v_prev_part_high_val;

            IF v_interval_days = 1/24 THEN
                v_interval_unit := 'HOUR';
            ELSIF v_interval_days = 1 THEN
                v_interval_unit := 'DAY';
            ELSIF v_interval_days IN (28,29,30,31) THEN
                -- 处理月粒度，取月份差
                v_interval_unit := 'MONTH';
            ELSE
                DBMS_OUTPUT.PUT_LINE('Unsupported partition interval for ' || v_table_owner || '.' || v_table_name);
                CONTINUE;
            END IF;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 只有一个分区，无法判断粒度，跳过或手动处理
                DBMS_OUTPUT.PUT_LINE('Only one partition exists for ' || v_table_owner || '.' || v_table_name || ', skip auto-creation');
                CONTINUE;
        END;

        -- 生成缺失的分区
        FOR i IN 1..v_num_part_to_create LOOP
            -- 根据粒度计算下一个分区边界
            CASE v_interval_unit
                WHEN 'HOUR' THEN
                    v_new_part_high_val := v_last_part_high_val + (1/24)*i;
                    v_new_part_name := 'P' || TO_CHAR(v_new_part_high_val, 'YYYYMMDDHH24');
                WHEN 'DAY' THEN
                    v_new_part_high_val := v_last_part_high_val + i;
                    v_new_part_name := 'P' || TO_CHAR(v_new_part_high_val, 'YYYYMMDD');
                WHEN 'MONTH' THEN
                    v_new_part_high_val := ADD_MONTHS(v_last_part_high_val, i);
                    v_new_part_name := 'P' || TO_CHAR(v_new_part_high_val, 'YYYYMM');
            END CASE;

            -- 检查分区是否已存在
            BEGIN
                SELECT 1
                FROM all_tab_partitions
                WHERE owner = v_table_owner 
                  AND table_name = v_table_name 
                  AND partition_name = v_new_part_name;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    -- 生成创建分区的SQL
                    v_create_sql := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name ||
                                    ' ADD PARTITION ' || v_new_part_name ||
                                    ' VALUES LESS THAN (TIMESTAMP ''' || TO_CHAR(v_new_part_high_val, 'YYYY-MM-DD HH24:MI:SS') || ''')' ||
                                    ' TABLESPACE ' || v_last_tablespace;

                    -- 执行SQL
                    EXECUTE IMMEDIATE v_create_sql;
                    DBMS_OUTPUT.PUT_LINE('Added partition: ' || v_new_part_name || ' for ' || v_table_owner || '.' || v_table_name);
            END;
        END LOOP;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END ADD_MISSING_PARTITIONS_FOR_ALL_TABLES;
/

关键优化点 #

• 自动识别分区列：从all_part_key_columns获取，无需硬编码
• 自动判断分区粒度：通过最后两个分区的边界差值识别小时/日/月粒度
• 动态生成分区名称和边界：根据粒度生成符合规则的分区名
• 异常处理：处理单分区表、不支持的粒度等场景
• 可配置预创建数量：通过v_num_part_to_create调整预创建的分区数量

配置自动调度任务 #

使用Oracle 11g的DBMS_SCHEDULER配置定时任务，每天执行一次存储过程：

-- 创建调度任务
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'ADD_MISSING_PARTITIONS_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'ADD_MISSING_PARTITIONS_FOR_ALL_TABLES',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- 每天凌晨2点执行
        enabled         => TRUE,
        comments        => 'Auto create missing partitions for DWH tables'
    );
END;
/

-- 查看任务状态
SELECT job_name, enabled, last_start_date, next_run_date
FROM user_scheduler_jobs
WHERE job_name = 'ADD_MISSING_PARTITIONS_JOB';

注意事项 #

• 确保执行用户有ALTER TABLE、SELECT相关系统权限（如SELECT ANY DICTIONARY）
• 测试阶段可先手动执行存储过程验证效果，再启用调度任务
• 对于特殊命名规则的分区表，可根据实际情况调整分区名称生成逻辑

内容的提问来源于stack exchange，提问作者microracle