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:
- 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:
ALTER INDEX index_name REBUILD
Share and contribute or get technical support and help at My Digital Life Forums.
Related Articles
- Modify and Cheat Windows Vista Experience Index Rating with SystemPoint
- WinGuggle Free Download to Get Vista Product Key or Change OEM Logo and Performance Index Score
- Porn Blog Has State Trooper in Hot Water
- Get Some Interesting Findings from Akamai’s State of the Internet Report
- Samsung New Foldable State-of-the-Art OLED Mobile Phone for the Future
- Check Oracle Version
- Oracle Database Link
- Oracle JDeveloper Reviews
- Samsung’s 1.8 Inch 64GB (SSD) Solid State Drive Into Mass Production
- BitMICRO Annouced 416GB SSD (Solid State Drive) Memory Module


























September 19th, 2006 22:28
What about with Oracle 9i option SKIP UNUSABLE INDEXES? Pls Explain.
March 6th, 2007 16:51
At least in oracle10 the status could also be ‘UNUSABLE’
February 6th, 2008 06:31
[...] ORA-01502 Oracle Index in Unusable State [...]
August 21st, 2008 14:49
thanks… the alter table fix worked