When trying to perform query on Oracle tables with select SQL statement, Oracle returns the following error:

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

he 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 indixes are left with STATUS = ‘INVALID’.

Invalid indexes can be checked with a SELECT * from USER_INDEXES WHERE STATUS = ‘INVALID’; SQL statement.

Solution to this error is simple. You can:

1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition

Generally, the following SQL manipulation language will be able to rebuild the unusable index:

ALTER INDEX index_name REBUILD