This content has been marked as final. Show 1 reply
Theoretically, if there is an entry in ctxsys.dr$index, then an index with that name exists. It may be that it exists in another schema or that it is case sensitive, so that you need to search for it in the exact case, enclosed within double quotes. Or it may be that you do not have sufficient privileges to see the index, so you get the same result as if the index does not exist. Sometimes, especially if an index is dropped with force when there are dependent objects, there may be leftover pieces in the data dictionaries.
Can you post the results of the following, substituting the index name for ... in exact case?
select owner, index_name from dba_indexes where index_name = "...";
select idx_name, idx_status from ctxsys.dr$index where idx_name = "...";