ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state.

This article details a potential solution to the following ORA-01502 return :

java.sql.SQLException: ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state.

Perform the following steps, in order :

1 ) Check "unstable" indexes on the USERPRINCIPAL table by running the following SQL against the product database :

• SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, COMPRESSION, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = 'USERPRINCIPAL' AND STATUS <> 'VALID';

2 ) Generate SQL to resolve the discovered "UNSTABLE" indexes :

• SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' AND TABLE_NAME = 'USERPRINCIPAL';

3 ) Copy the output from 'point #2' and execute.

4 ) Verify that the "unstable" indexes have been corrected by running the SQL detailed within 'point #1' once more.

If the issue persists, please submit a ticket to be review by the product support team.