ORA-01502: index ‘string.string’ or partition of such index is in unusable state

ORA-01502: index ‘string.string’ or partition of such index is in unusable state


The error indicates an attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations.
This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace,
all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.



SELECT 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
FROM all_indexes
WHERE owner = 'HONNIKERY' AND status = 'VALID'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'))));

Index Status Types in DBA_INDEXES

SELECT DISTINCT STATUS FROM DBA_INDEXES;
STATUS
-----------
N/A
UNUSABLE
VALID



ORA-01502: index ‘string.string’ or partition of such index is in unusable state


SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:
SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';