Solved

How to drop or rebuild a index which is in unusable status, i tried dropping it but its giving an error that such index name is not found, but the table has that index?

Posted on 2011-03-22
5
367 Views
Last Modified: 2012-05-11
How to Drop an unusable index or rebuild it?

i tried the normal rebuild and drop commands , but it did not work, it says such index name could not be found, but i'm seeing the index in the table in unusable status?

0
Comment
Question by:D-pk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35192326
What index type is it?

Did you try FORCING the drop?

drop index indexname force;
0
 

Author Comment

by:D-pk
ID: 35192391
I tried using Force , got an error....................

ORA-29862: cannot specify FORCE option for dropping non-domain index

Its a NORMAL INDEX.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 35192434
Could it be that the owner of the index is not the owner of the table?  Check those with this query:

select owner, index_name, table_owner, table_name
from dba_indexes
where table_owner = '<own>' and
table_name = '<name>';

0
 

Author Closing Comment

by:D-pk
ID: 35192508
The owner of the Index and the table are the same, i guess i don't have the permission to Drop the index.
0
 
LVL 21

Expert Comment

by:Amitkumar Panchal
ID: 35192584
Kindly check the following.

1. Is the index created by CREATE INDEX statement?
    Yes, Use the DROP INDEX <INDEXNAME> to drop the index.

2. Is the index created automatically by defining a UNIQUE KEY or PRIMARY KEY constraint on a table?
    Yes,
     a. Disable a constraint using below syntax first and then drop an index.
         ALTER TABLE <TABLENAME> DISABLE constraint <CONSTRAINTNAME>

     OR

     b. Drop a constraint using below syntax first and then drop an index.
         ALTER TABLE <TABLENAME> DROP constraint <CONSTRAINTNAME>
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

623 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