• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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?

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
D-pk
Asked:
D-pk
1 Solution
 
slightwv (䄆 Netminder) Commented:
What index type is it?

Did you try FORCING the drop?

drop index indexname force;
0
 
D-pkAuthor Commented:
I tried using Force , got an error....................

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

Its a NORMAL INDEX.
0
 
johnsoneSenior Oracle DBACommented:
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
 
D-pkAuthor Commented:
The owner of the Index and the table are the same, i guess i don't have the permission to Drop the index.
0
 
Amitkumar PSr. ConsultantCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now