Oracle分区表：当前分区索引置为Unusable、重建及脚本问题修复

问题概述 #

有一张按T_DATE列做INTERVAL日分区的Oracle表，需要在加载数据到指定日期分区前，将对应索引置为Unusable，加载完成后重建这些索引。原脚本存在变量未声明、分区名判断错误的问题，导致编译失败。

错误原因分析 #

1. 变量未声明：脚本中使用了v_partition_name但未在DECLARE段定义，触发PLS-00201错误。
2. 分区名判断逻辑错误：INTERVAL分区的系统生成分区名（如SYS_P18549）不是日期格式字符串，直接用TO_DATE(partition_name, 'YYYY-MM-DD')无法匹配目标日期，需通过high_value字段判断分区对应的日期范围。

修正后的完整脚本 #

1. 将指定日期分区的索引置为Unusable的脚本 #

DECLARE
  v_table_owner    VARCHAR2(30) := 'TEST_USER';
  v_table_name     VARCHAR2(30) := 'TEST_TABLE';
  v_partition_date DATE         := TO_DATE('2023-11-01', 'YYYY-MM-DD');
  v_partition_name VARCHAR2(30); -- 新增变量声明
BEGIN
  -- 根据目标日期匹配对应的分区名（处理INTERVAL分区的high_value）
  SELECT partition_name
    INTO v_partition_name
    FROM all_tab_partitions
   WHERE table_owner = v_table_owner
     AND table_name = v_table_name
     -- 转换high_value为日期，匹配目标日期所在分区
     AND v_partition_date < TO_DATE(SUBSTR(high_value, INSTR(high_value, '''')+1, 19), 'YYYY-MM-DD HH24:MI:SS')
     AND v_partition_date >= COALESCE(TO_DATE(SUBSTR(low_value, INSTR(low_value, '''')+1, 19), 'YYYY-MM-DD HH24:MI:SS'), 
                                     TO_DATE('0001-01-01', 'YYYY-MM-DD'));

  -- 遍历所有索引，区分本地和全局索引处理
  FOR rec_index IN (SELECT owner, index_name, index_type
                      FROM all_indexes
                     WHERE table_name = v_table_name
                       AND table_owner = v_table_owner) LOOP
    IF rec_index.index_type = 'NORMAL' THEN -- 全局索引，只能置为整体Unusable
      EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' UNUSABLE';
    ELSIF rec_index.index_type = 'LOCAL' THEN -- 本地索引，可以置对应分区为Unusable
      EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' UNUSABLE PARTITION ' || v_partition_name;
    END IF;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('未找到对应日期的分区：' || TO_CHAR(v_partition_date, 'YYYY-MM-DD'));
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('执行错误：' || SQLERRM);
END;
/

2. 重建Unusable索引的优化脚本 #

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name  VARCHAR2(30) := 'TEST_TABLE';
BEGIN
  -- 重建全局Unusable索引
  FOR rec_index IN (SELECT owner, index_name
                      FROM all_indexes
                     WHERE table_name = v_table_name
                       AND table_owner = v_table_owner
                       AND status = 'UNUSABLE') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
      DBMS_OUTPUT.PUT_LINE('已重建全局索引：' || rec_index.owner || '.' || rec_index.index_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('重建全局索引失败：' || rec_index.owner || '.' || rec_index.index_name || '，错误信息：' || SQLERRM);
    END;
  END LOOP;

  -- 重建本地索引的Unusable分区
  FOR rec_partition IN (SELECT aip.index_owner,
                               aip.index_name,
                               aip.partition_name
                          FROM all_ind_partitions aip
                         WHERE aip.status = 'UNUSABLE'
                           AND aip.table_owner = v_table_owner
                           AND aip.table_name = v_table_name) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name || ' REBUILD PARTITION ' || rec_partition.partition_name;
      DBMS_OUTPUT.PUT_LINE('已重建本地索引分区：' || rec_partition.index_owner || '.' || rec_partition.index_name || '.' || rec_partition.partition_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('重建本地索引分区失败：' || rec_partition.index_owner || '.' || rec_partition.index_name || '.' || rec_partition.partition_name || '，错误信息：' || SQLERRM);
    END;
  END LOOP;
END;
/

关键注意事项 #

• 索引类型区分：全局索引无法单独将某个分区置为Unusable，只能将整个索引设为Unusable；本地索引支持针对分区操作。
• INTERVAL分区匹配：系统自动生成的INTERVAL分区名无日期含义，需通过high_value和low_value字段判断分区对应的日期范围，注意这两个字段是LONG类型，需通过字符串截取转换为日期。
• 异常处理：添加了分区不存在、执行错误的异常捕获，便于排查问题。

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