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

Turning off overwrite/overtype mode

I had a little irritating problem today when I am typing in WhatApp Web. For some reason, the text in the textbox is always in overtype mod...