not able to drop and recreate the oracle text index

Question

I am not able to drop and recreate the oracle text index.

SQL> drop index "WBR"."CTX_t1";

Index dropped

SQL> 
SQL> CREATE INDEX "WBR"."CTX_t1"
  2  ON WBR.t1(ASSET_XML)
  3  INDEXTYPE IS "CTXSYS"."CONTEXT"
  4  PARAMETERS ('DATASTORE CTXSYS.DIRECT_DATASTORE SECTION GROUP CTXSYS.AUTO_SECTION_GROUP SYNC (every "SYSDATE+10/1440")')
  5  ;

CREATE INDEX "WBR"."CTX_t1"
ON WBR.t1(ASSET_XML)
INDEXTYPE IS "CTXSYS"."CONTEXT"
PARAMETERS ('DATASTORE CTXSYS.DIRECT_DATASTORE SECTION GROUP CTXSYS.AUTO_SECTION_GROUP SYNC(every "SYSDATE+10/1440")')

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10507: duplicate index name: CTX_t1
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

Even after dropping the cintext index, I can see the entry in CTXSYS.CTX_INDEXES.

The job and internal tables are still there in database:

DR$CTX_t1$I
DR$CTX_t1$J--job
DR$CTX_t1$K
DR$CTX_t1$N
DR$CTX_t1$R
DR$CTX_t1$X

any suggestions?

post the result of `select owner, object_name, object_type from dba_objects where upper(object_name) like '%CTX_T1%';`and your Oracle version, please. Are you connected with the user **WBR** or with other user? — Marmite Bomber, Sep 30 '15 at 15:41
I am not logged in as WBR user. I am trying the above mentioned tasks from another schema ... on facing errors...I even tried doing same stuff from DBA schema but failed.same error. — Prashant Mishra, Sep 30 '15 at 17:51

score 2 · Answer 1 · answered Sep 30 '15 at 17:05

2

1) check if there exists an other object with the same name (possible case insensitive)

select owner, object_name, object_type from dba_objects where upper(object_name) like '%CTX_T1%';

if yes drop it.

2) try drop the index with the FORCE option

drop index "WBR"."CTX_t1" FORCE;

if it doesn't help:

3) contact Oracle support

answered Sep 30 '15 at 17:05

Marmite Bomber

19,886
4
26
53

have tried FORCE clause as well.Even this was not working.Same error – Prashant Mishra Sep 30 '15 at 17:52

score 2 · Answer 2 · answered Nov 26 '19 at 11:33

it seems a bug on oracle. We have a script who refresh our quality database from the production database. To solve this problem, we execute this PL/SQL script before refresh begin.

    /**
     * Correctif bug sur la suppression d'index full text: 
     * Unable to recreate an Oracle Text index as the database continually throws a "DRG-10507: duplicate index name" error
     * http://ask4dba.blogspot.com/2016/11/drg-10507-in-creating-text-index-after.html
     * https://www.ibm.com/support/pages/unable-recreate-oracle-text-index-database-continually-throws-drg-10507-duplicate-index-name-error
    **/
    DECLARE
      LR$IDX_ID NUMBER(15);
      LR$IDX_NAME VARCHAR2(255);
      LR$SCHEMA VARCHAR2(255);

    CURSOR C1 IS

            select IDX_ID, IDX_NAME, usr.USERNAME from CTXSYS.DR$INDEX
            join all_users usr on usr.USER_ID = IDX_OWNER#
            where IDX_NAME in ('IDX_EDI_EMISSION_MSG_EAI', 'IDX_EDI_EMISSION_MSG', 'IDX_EDI_RECEPTION_MSG', 'IDX_EDI_RECEPTION_MSG_TMS');

    BEGIN 

        DBMS_OUTPUT.put_line('DEBUT de la suppression des index fulltext' );
        OPEN C1;
        LOOP 
            FETCH C1 INTO  LR$IDX_ID, LR$IDX_NAME, LR$SCHEMA;
            EXIT WHEN C1%NOTFOUND; 

            BEGIN

                BEGIN

                    DBMS_OUTPUT.put_line('Suppression (drop) de l''index '|| LR$SCHEMA || '.' || LR$IDX_NAME );
                    execute immediate 'DROP INDEX ' || LR$SCHEMA || '.' || LR$IDX_NAME;

                EXCEPTION
                    WHEN OTHERS THEN

                        DBMS_OUTPUT.put_line('Erreur Oracle #'||TO_CHAR(SQLCODE)||' : '||SQLERRM(SQLCODE));
                        DBMS_OUTPUT.put_line('L''index ' || LR$IDX_NAME ||' n''existe pas :  ' || LR$IDX_ID);

                END;

                DBMS_OUTPUT.put_line('Suppression des lignes de l''index ' || LR$IDX_ID);
                delete from ctxsys.dr$index_value where IXV_IDX_ID = LR$IDX_ID;
                delete from ctxsys.dr$index_object where IXO_IDX_ID = LR$IDX_ID;
                delete from ctxsys.dr$index where idx_id = LR$IDX_ID;
                commit;

            END;                       

        END LOOP;

        CLOSE C1;
        COMMIT;
    End ;
    /

This is right solution (WO) How To Resolve Rebuild Help Search Index Errors Due To Duplicate Index Name? (Doc ID 1369248.1) — ibre5041, Jan 13 '20 at 13:47

score 1 · Answer 3 · edited Jul 02 '20 at 04:07

1

[1] Oracle Text: Create Or Alter Index Fails With DRG-50857, ORA-27486 ( Doc ID 2497828.1 )

grant create job to the user and then try to create the indexes, it should work.

I had a situation of changing index sync parameter from ('SYNC (on commit)') to ('SYNC (EVERY "SYSDATE+15/1440")'), as this will automatically use a Oracle job, the schema should have create job privilege.

edited Jul 02 '20 at 04:07

alexherm

1,362
2
18
31

answered Jul 01 '20 at 20:58

Kulkarni Girish

11
1

score 0 · Answer 4 · answered May 29 '17 at 13:08

it's not an answer basically, for those who get a slightly different error (i'm on Oracle 12c) during create index and right after drop index statements:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at "CTXSYS.DRUE", line 171
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 316

there seems to be a time lag (or something else) between drop of the index, and the cleanup of the related data from the CTXSYS tables (but i didn't need any commit statements). so for me, executing drop of the index AGAIN and running the same create statement AGAIN resolved the issue (the second create runs OK)

not able to drop and recreate the oracle text index

4 Answers4