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.