Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
370 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

704 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