Oracle error Index is in unusable state

Posted on 2007-08-10
Medium Priority
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
  • 2
LVL 143

Expert Comment

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

Expert Comment

by:Naveen Kumar
ID: 19668722
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

ID: 19672097
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

oleggold earned 1500 total points
ID: 19672118
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:

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

807 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