ORA-01502 Oracle Index in Unusable State

»»»ORA-01502 Oracle Index in Unusable State
When trying to perform query on Oracle tables with select SQL statement, Oracle returns the following error:

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

The error indicates 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.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations. This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace, all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.

Invalid indexes can be checked with a SELECT * from USER_INDEXES WHERE STATUS = ‘INVALID’; SQL statement.

Solution to this error is simple. You can:

  1. Drop the specified index and/or recreate the index
  2. Rebuild the specified index
  3. Rebuild the unusable index partition

Generally, the following SQL manipulation language will be able to rebuild the unusable index:

ALTER INDEX index_name REBUILD

By |2016-12-09T08:43:28+00:00December 9th, 2016|Categories: Databases|Tags: |10 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.