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
Related posts:
- Oracle Database Import Error 3113/3114
- ORA-25153 Temporary Tablespace is Empty Error in Oracle
- Check Oracle Version
- Oracle Database Link
- Insert Text Literal and Concatenation in Oracle SQL
The status should be unusable not invalid
Use the below query to find out the invalid indexes
SELECT index_name, status FROM dba_INDEXES WHERE status=''UNUSABLE'
Thanks !!!
rebuilding indexes let me insert record. Thanks!
Error was:-
[row:3,col:1] ORA-01502: 索引'PK_EMP'またはそのパーティションが使用不可の状態です。
(index PK_EMP or its partition are in unusable state)
Your first statement needs to check for 'UNUSABLE' and not 'INVALID' in USER_INDEXES.
I think Oracle is having only index status as Usable/Unusabe.
"Skip Unusable index" will allow the all the DML(Inclusing Select) operations on the table.
Thanks & Regards,
Deepak
thanks… the alter table fix worked
[...] ORA-01502 Oracle Index in Unusable State [...]
At least in oracle10 the status could also be 'UNUSABLE'
What about with Oracle 9i option SKIP UNUSABLE INDEXES? Pls Explain.