Oracle 脚本：修复 status 为 unusable 的 index
【Oracle 脚本：修复 status 为 unusable 的 index】
在Oracle数据库的日常维护中，索引（Index）的状态管理是保障查询性能的关键环节。当索引因表结构变更、分区操作异常或存储空间问题导致状态变为UNUSABLE时，会直接影响SQL语句的执行效率，甚至引发ORA-01502错误。本文将系统阐述UNUSABLE索引的成因、诊断方法及修复脚本，帮助DBA快速恢复数据库性能。
一、UNUSABLE索引的成因分析
1.1 表结构变更引发索引失效
当执行ALTER TABLE修改列数据类型（如VARCHAR2转NUMBER）、增减列或调整列顺序时，若未同步重建索引，Oracle会自动将相关索引标记为UNUSABLE。例如：
ALTER TABLE employees MODIFY (salary NUMBER(10,2));
-- 执行后需检查EMPLOYEES_PK等索引状态1.2 分区表操作异常
对分区表执行SPLIT PARTITION、MERGE PARTITION等操作时，若分区索引未正确维护，会导致全局索引失效。典型场景：
ALTER TABLE sales SPLIT PARTITION p2023 AT (TO_DATE('01-JUL-2023','DD-MON-YYYY'))
INTO (PARTITION p2023q1, PARTITION p2023q2);
-- 需重建全局索引SALES_GLOBAL_IDX1.3 存储空间问题
表空间不足、数据文件损坏或磁盘I/O错误可能导致索引段无法扩展，触发UNUSABLE状态。通过以下查询可定位问题：
SELECT tablespace_name, file_name, bytes/1024/1024 "Size(MB)"
FROM dba_data_files
WHERE tablespace_name IN (
SELECT tablespace_name FROM dba_indexes
WHERE status='UNUSABLE'
);二、诊断UNUSABLE索引的方法
2.1 查询UNUSABLE索引列表
使用DBA_INDEXES视图获取全库UNUSABLE索引信息：
SELECT owner, index_name, table_name, tablespace_name, status
FROM dba_indexes
WHERE status='UNUSABLE'
ORDER BY owner, table_name;2.2 检查分区索引状态
对于分区表，需检查分区级索引状态：
SELECT index_owner, index_name, partitioning_type, status
FROM dba_part_indexes
WHERE status='UNUSABLE';2.3 分析依赖对象
确认UNUSABLE索引是否被视图、存储过程等对象依赖：
SELECT name, type, referenced_name, referenced_type
FROM dba_dependencies
WHERE referenced_name IN (
SELECT index_name FROM dba_indexes WHERE status='UNUSABLE'
);三、修复UNUSABLE索引的脚本方案
3.1 单个索引重建脚本
基础重建命令（需确保表空间有足够空间）：
ALTER INDEX schema_name.index_name REBUILD
TABLESPACE index_tablespace
PARALLEL 4;3.2 批量重建脚本（按Schema）
生成特定用户下所有UNUSABLE索引的重建脚本：
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' PARALLEL 4;'
FROM dba_indexes
WHERE status='UNUSABLE'
AND owner='YOUR_SCHEMA';
-- 执行结果需DBA审核后运行3.3 分区索引修复方案
3.3.1 局部分区索引修复
ALTER INDEX schema_name.index_name REBUILD PARTITION partition_name
TABLESPACE index_tablespace;3.3.2 全局分区索引修复（需ONLINE选项减少锁表）
ALTER INDEX schema_name.global_index_name REBUILD ONLINE
TABLESPACE index_tablespace
PARALLEL 8;3.4 大表索引优化重建
对于超过100GB的表，建议采用NOLOGGING模式减少重做日志生成：
ALTER INDEX schema_name.large_index REBUILD NOLOGGING
TABLESPACE index_tablespace
COMPUTE STATISTICS;四、预防UNUSABLE索引的最佳实践
4.1 变更前检查脚本
在执行ALTER TABLE前预检查索引依赖：
CREATE OR REPLACE PROCEDURE check_index_dependency(p_table IN VARCHAR2) IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM dba_indexes
WHERE table_name = UPPER(p_table) AND status='VALID';
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('警告：表'||p_table||'存在'||v_count||'个有效索引，建议先重建');
END IF;
END;
/4.2 自动化监控脚本
设置每日监控任务检测UNUSABLE索引：
-- 创建监控表
CREATE TABLE index_status_monitor (
monitor_date DATE,
owner VARCHAR2(30),
index_name VARCHAR2(30),
table_name VARCHAR2(30),
status VARCHAR2(12)
);
-- 每日插入监控数据
INSERT INTO index_status_monitor
SELECT SYSDATE, owner, index_name, table_name, status
FROM dba_indexes
WHERE status='UNUSABLE';4.3 分区表操作规范
执行分区操作时添加UPDATE INDEXES子句：
ALTER TABLE sales SPLIT PARTITION p2023 AT (TO_DATE('01-JUL-2023','DD-MON-YYYY'))
INTO (PARTITION p2023q1, PARTITION p2023q2)
UPDATE INDEXES;五、特殊场景处理
5.1 RAC环境下的并行重建
在RAC集群中需指定INSTANCE参数：
ALTER SESSION ENABLE PARALLEL DML;
ALTER INDEX schema_name.cluster_index REBUILD
TABLESPACE index_tablespace
PARALLEL 8
INSTANCE 1;5.2 压缩索引修复
对于压缩索引，重建时需保留压缩属性：
ALTER INDEX schema_name.compressed_index REBUILD
TABLESPACE index_tablespace
COMPRESS;5.3 域索引特殊处理
处理Oracle Text域索引时需调用特定API：
BEGIN
CTX_DDL.REBUILD_INDEX('schema_name.text_index');
END;
/六、性能优化建议
6.1 并行度选择
根据CPU核心数设置合理并行度：
-- 查询可用CPU数
SELECT COUNT(*) FROM v$thread WHERE status='OPEN';
-- 设置并行度（建议为CPU核心数的1/2）
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;6.2 存储参数优化
重建时指定最优存储参数：
ALTER INDEX schema_name.perf_index REBUILD
STORAGE (INITIAL 256M NEXT 128M MAXEXTENTS UNLIMITED)
TABLESPACE index_tablespace;6.3 统计信息收集
重建后立即收集统计信息：
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME','INDEX_NAME');【关键词】Oracle索引、UNUSABLE状态、索引重建、分区索引、DBA脚本、性能优化、RAC环境、域索引
【简介】本文详细解析Oracle数据库中索引状态变为UNUSABLE的成因，提供从诊断到修复的全流程解决方案。涵盖单个/批量索引重建脚本、分区索引处理、RAC环境特殊处理等场景，并给出预防性监控和性能优化建议，帮助DBA高效维护数据库索引健康状态。
