Oracle indexes can go into UNUSABLE state after a maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.
Symptoms:
Queries and any other operations against a table with unusable indexes will generate errors as below,
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
Cause:
Normal Indexes:
SELECT owner, index_name, tablespace_name
FROM dba_indexes
WHERE status = 'UNUSABLE';
Index Partition:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';
Index Sub-partition:
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name
FROM dba_ind_SUBPARTITIONS
WHERE status = 'UNUSABLE';
Solution:
Normal Index:
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' sql_to_rebuild_index
FROM dba_indexes
WHERE status = 'UNUSABLE';
Index Partition:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index
FROM dba_ind_partitions
WHERE status = 'UNUSABLE';
Index Sub-partition:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE';
Fix using single PL/SQL block:
set serveroutput on size unlimited
BEGIN
FOR x IN
(
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE PARALLEL' comm
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' ONLINE PARALLEL'
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE PARALLEL'
FROM dba_ind_SUBPARTITIONS
WHERE status = 'UNUSABLE'
)
LOOP
dbms_output.put_line(x.comm);
EXECUTE immediate x.comm;
END LOOP;
END;
/
Hope this blog was useful.
Regards,
Balaaji Dhananjayan
Comments