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
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?

Question by:D-pk
LVL 77

Expert Comment

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

Did you try FORCING the drop?

drop index indexname force;

Author Comment

ID: 35192391
I tried using Force , got an error....................

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

LVL 35

Accepted Solution

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>';


Author Closing Comment

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.
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?
     a. Disable a constraint using below syntax first and then drop an index.


     b. Drop a constraint using below syntax first and then drop an index.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Function to return one result based on data in first query 11 53
UTL_FILE invalid file operation 5 45
oracle sqlplus query delimiter 8 35
error doing substr 3 33
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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 …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

680 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