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 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 - ora-01502 index or partition of such index is in unusable state - oracle index unusable - oracle unusable index - ORA-01502: index or partition of such index is in unusable state - ora 01502 - index unusable - alter index unusable - index UNUSABLE oracle - unusable index in oracle - ORA-01502: index or partition of such index is in unusable state - index in unusable state - unusable index - ORA-01502: - index or partition of such index is in unusable state - ORA-01502: index - ORA-01502 - oracle alter index unusable - unusable index oracle - partition of such index is in unusable state in oracle - ORA-01502: index - oracle index status - ora-01502 index - oracle index unusable state - rebuild unusable indexes - oracle ORA-01502 - ora01502 - oracle index status unusable - ORA-01502: - partition of such index is in unusable state oracle - index is in unusable state - oracle unusable INDEXES - index status oracle - ora-01502 partition of such index is in unusable state - oracle index is in unusable state - ora 01502 partition of such index is in unusable state - oracle index invalid - alter index usable - index usable oracle - oracle index or partition of such index is in unusable state - of such index is in unusable state - oracle unusable index rebuild - index unusable state - alter index unusable oracle - index status unusable - oracle unusable index partition - partition of index is in unusable state - index status unusable oracle - oracle 01502 - oracle index unusable rebuild -