Oracle error Index is in unusable state

Posted on 2007-08-10
Last Modified: 2013-12-18
Hi, I'm getting this error msg which I'm not sure what is this meaning for, I did checked the index status and it look OK

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TRW_ADMIN                     TRW_AUDIT_PK                   VALID

The error message we are getting is as below:
"ORA-01502: index 'TRW_ADMIN.TRW_AUDIT_PK' or partition of such index is in unusable state"
Question by:motioneye
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    when the table is partitionned, did you check the index partitions also?
    LVL 28

    Expert Comment

    rebuilding the index should solve the issue.

    alter index idx1 rebuild;

    As already updated, you can check whether it is a partitioned index ?
    LVL 21

    Expert Comment

    If partitioned ,You can rebuild the index locally and even in parallel,all faster then global index,if it's not partitioned just use the kind example above or drop and create the index - may even work faster then rebuild:
    drop index idx...;
    create index idsx on mm() ...
    LVL 21

    Accepted Solution

    Sorry,what You decribe is regular for PKs,so it's the unique index of the PK that You need to rebuild.It's generally easier to disable the PK:
    alter table myt disable pk;
    and enable it again:
    alter table myt ENable pk EXCEPTIONS INTO EXCEPTIONS_TAB;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now