ORA-01502 index or partition of such index is in unusable state

book

Article ID: 224141

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM On Premise

Issue/Introduction

We are unable to navigate through Clarity pages or run any jobs and processes. 
The error on the log files refers to a problem with an index

Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01502: index 'NIKU.CMN_SEC_ASSGND_OBJ_PERM_PK' or partition of such index is in unusable state
ORA-06512: at "NIKU.CMN_SEC_ASSIGN_INST_RIGHT_SP", line 76
ORA-06512: at line 1

Cause

This is an Oracle DB issue:

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

Resolution

1. Make a DB backup.
2. Stop the Clarity services
3. On the database side, rebuild the affected index(es) using the command below as an example:
alter index <index name> rebuild
On your case: alter index CMN_SEC_ASSGND_OBJ_PERM_PK rebuild
4. Restart the Clarity services

 

Rebuilding the affected index might complain about the following index and so on.

If you find yourself in this scenario, you might need to rebuild all indices.

Additional Information

ORA-01502: index or partition of such index is in usable state problem