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

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.

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

  1. Bob
    April 16th, 2009 03:14
    6

    Your first statement needs to check for ‘UNUSABLE’ and not ‘INVALID’ in USER_INDEXES.

  2. Deepak B Sholapurkar
    January 31st, 2009 04:24
    5

    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

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

    thanks… the alter table fix worked

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

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

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

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

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

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

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 My Digital Life Comments RSS feed to receive notification of latest comments posted.
« Gmail Delete Button
IMP-00013 Oracle Import Error »

New Articles

Incoming Search Terms for the Article

ORA-01502 - oracle index unusable - ora-01502 index is in unusable state - ora-01502 index or partition of such index is in unusable state - ora 01502 - ORACLE UNUSABLE INDEX - ORA-01502: index or partition of such index is in unusable state - ora-01502 index - ORA-01502: - unusable index in oracle - index unusable - ORACLE index status - unusable index oracle - alter index unusable - index is in unusable state - index unusable oracle - partition of such index is in unusable state in oracle - index unusable state oracle - unusable index - index in unusable state - ORA-01502: index - ORA-01502: index or partition of such index is in unusable state - ORA-01502: index - oracle check index status - index status unusable - alter index unusable oracle - oracle ORA-01502 - REBUILD UNUSABLE INDEXES - oracle unusable indexes - ORA-01502 - or partition of such index is in unusable state - oracle index unusable state - index unusable state - oracle index partition unusable - oracle index status unusable - ORA-01502: - oracle indexes unusable - error ora-01502 index or partition of such index is in unusable state - oracle Unusable - index status unusable oracle - rebuild unusable index - ORA-01502 - oracle index is in unusable state - invalid index oracle - oracle invalid index - oracle alter index unusable - ora-01502 error - oracle unusable index partition - index in unusable state in oracle - oracle index unusable why - index status in oracle - oracle invalid indexes - oracle index invalid - invalid index in oracle - ora 1502 - index status is unusable - unusable indexes - index oracle unusable - alter index rebuild unusable - index unusable in oracle - unusable indexes in oracle - why index are in unusable state - ORA-01502: partition of such index is in unusable state - index partition unusable - oracle rebuild all unusable indexes - ORACLE-1502 partition of such index is in unusable state - ora01502 - finding unusable indexes - oracle index status value - REBUILD the unusable index partition - index marked unusable - oracle index or partition of such index is in unusable state - index or partition of such index is in unusable state - oracle index in unusable state - ORA-01502 partition of such index is in unusable state -