Thursday, April 19, 2018

Oracle - ORA-01502: index XXX.YYYY or partition of such index is in unusable state

If you encounter an Oracle error

ORA-01502: index XXX.YYYY or partition of such index is in unusable state

You could apply the following fix at the database

1. Log into the database, for example

sqlplus sys as sysdba

2. Run the following SQL

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

3. The above SQL will return multiple Alter statement which the index has to be rebuild. For example

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
ALTER INDEX MYUSER.TABLE1 REBUILD;
ALTER INDEX MYUSER.TABLE2 REBUILD;

4. Run all of the alter statement to make the index usable again and it should fix the issue.

Reference:

1. http://dba.stackexchange.com/questions/3754/ora-01502-index-or-partition-of-such-index-is-in-usable-state-problem

No comments:

Post a Comment

Oracle - ORACLE error 959 encountered when importing with imp

If the dump is exported with imp, you cannot use impdp and you may face issue such as IMP-00003: ORACLE error 959 encountered ORA-009...