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

Share and contribute or get technical support and help at My Digital Life Forums.



This entry was posted on Saturday, January 28th, 2006 at 4:07 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “ORA-01502 Oracle Index in Unusable State”

  1. sandeep
    September 19th, 2006 22:28
    1

    What about with Oracle 9i option SKIP UNUSABLE INDEXES? Pls Explain.

  2. Kalle
    March 6th, 2007 16:51
    2

    At least in oracle10 the status could also be ‘UNUSABLE’

  3. ORA-01502: Indexes in unusable state « PSST0101
    February 6th, 2008 06:31
    3

    [...] ORA-01502 Oracle Index in Unusable State [...]

  4. sabry
    August 21st, 2008 14:49
    4

    thanks… the alter table fix worked

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.
« Gmail Delete Button
IMP-00013 Oracle Import Error »

Custom Search

New Articles

Incoming Search Terms for the Article

ora-01502 - oracle index unusable - index unusable - ora 01502 - ORA-01502: - oracle unusable index - Unusable index - ora-01502: index - ORACLE index status - index UNUSABLE oracle - ORA-01502: index - ORA-01502: - unusable Index Oracle - ORA-01502 - oracle disable index - oracle ora-01502 - oracle index unusable state - index is in unusable state - unusable indexes - oracle unusable indexes - ORA-01502 - alter index unusable - oracle invalid index - "ORA-01502" - skip unusable index - index unusable state - rebuild unusable indexes - index status unusable - oracle index status unusable - ORA-01502 ORACLE - all - oracle index is in unusable state - UNUSABLE index in oracle - oracle index in unusable state - rebuild unusable index - unusable indexes in oracle - ORA-01502: index partition of such index is in unusable state - SKIP UNUSABLE INDEXES - ORA - 01502 - index status oracle - oracle rebuild unusable index - oracle index disable - invalid index oracle - oracle 01502 - invalid indexes - Rebuild the unusable index partition - index unusable state oracle - oracle index state - disable index oracle - ORA:01502 - Oracle Indexes Unusable - Index status in oracle - ORACLE INDEX INVALID - index partition unusable - oracle check index status - oracle alter index unusable - oracle partition index unusable - index in unusable state oracle - UNUSABLE oracle index - unusable indexes oracle - oracle skip unusable - index unusable in oracle - INDEX ORACLE UNUSABLE - oracle unusable - rebuild unusable local indexes - ORA-01502 + oracle - ORA-01502 error - oracle partition of such index is in unusable state - oracle index usable - index in unusable state - index status unusable oracle - disable index in oracle - why Index unusable - oracle ora 01502 - oracle unusable state -