ORA-01502: index ‘string.string’ or partition of such index is in unusable state

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

Cause: 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

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition

Workaround:

Run the following script to check the unused indexes and some important details:

SELECT TABLE_NAME, TABLE_OWNER, INDEX_NAME, BLEVEL, PARTITIONED, STATUS FROM USER_INDEXES WHERE STATUS=’UNUSED’;

Run following query to rebuild the unused index:

ALTER INDEX INDEX_NAME REBUILD;

To rebuild unusable partitioned index:

ALTER INDEX INDEX_NAME REBUILD PARTITION PARTITION_NAME;

If number of unused indexes are greater than one then use following sql to get script to rebuild entire unused indexes of that particular schema and run that script to rebuild the same:

SELECT ‘ ALTER INDEX ‘ || INDEX_NAME || ‘ REBUILD ; ‘ FROM USER_INDEXES WHERE STATUS=’UNUSED’;

Other Workarounds:

To prevent ORA-01502 errors ,set parameter skip_unusable_indexes=true after that optimizer selects an execution plan that does not utilize unusable indexes


However, when you skip an index you risk getting a sub-optimal SQL execution plan. And since the SQL statement generates no error, the end-user is unaware that the optimizer has chosen a plan that was less than optimal and only notices excessive response time.


When you skip an unusable index, the database records an error message in the alert log file whenever an index is marked unusable.

NOTE: In previous releases of Oracle, the skip_unusable_indexes parameter could only be used at session level and the default value was False. Now, you will be able to set this parameter at session level and instance level as well.

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply