1 Reply Latest reply on Oct 19, 2012 10:54 PM by Barbara Boehmer

    CTXSYS drop an index from dr$index

      Hi All,
      I'm not that good with Oracle Text so I would like to ask some experts on how to remove an index from ctxsys.dr$index and all subsequent tables in the ctxsys schema.

      An Oracle Text index was created as the user SYS and I found entry in ctxsys.dr$index. The index doesn't exist in the SYS schema so I would like to know if there is a function to delete an index with a certain ID from all table resided in the ctxsys schema dr$index etc.

      Hope there is a function to do this.

      Thanks for any help.

      Edited by: user10148486 on 19-okt-2012 2:21
        • 1. Re: CTXSYS drop an index from dr$index
          Barbara Boehmer
          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 = "...";