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:
Drop the specified index and/or recreate the index
Rebuild the specified index
Rebuild the unusable index partition
Generally, the following SQL manipulation language will be able to rebuild the unusable index:
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+.
