Can't Drop Text Domain Index Which Came Imported To 11.1.0.7 from 10.2.0.4 Export Due to ORA-29868 (Doc ID 751883.1)

Last updated on JUNE 01, 2016

Applies to:

Oracle Text - Version 10.2.0.4 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 17-Apr-2013***


Symptoms

Some Text index DDL sql statements failed during Data Pump Import due to non-existent tablespace used in  the Text index storage preferences, e.g.

Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX failed to create with error:
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-00959: tablespace 'TEXT_INDEX' does not exist
Failing sql is:
CREATE INDEX "TEXT_USER"."TEXT_DOC_META_INDEX" ON "TEXT_USER"."TEXT_DOCUMENT" ("METADATA")  INDEXTYPE IS "CTXSYS"."CONTEXT"  PARAMETERS ('filter ctxsys.null_filter STOPLIST CTXSYS.EMPTY_STOPLIST memory 200M storage text_idx
ORA-39083: Object type INDEX failed to create with error:
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-00959: tablespace 'TEXT_INDEX' does not exist


Running "drop index text_user.text_doc_meta_index force" gives the following error:

ERROR at line 1:
ORA-29868: cannot issue DDL on a domain index marked as LOADING


Attempting to drop the user with "drop user text_user cascade" gives the following error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-29868: cannot issue DDL on a domain index marked as LOADING

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms