Text Indexes In Import State And Owned Incorrectly By SYS After DataPump export / import DataPump (Doc ID 744162.1)

Last updated on JULY 12, 2017

Applies to:

Oracle Text - Version 10.1.0.2 to 11.1.0.6 [Release 10.1 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 05-Sep-2014***


Symptoms

-- Problem Statement:
Having problems while importing data with DataPump, impdp throws some errors and Text indexes are owned by SYS and have IMPORT status.

I have already tried : drop index SYS.TEXT_METADATA_DOC_INDEX force; this failed with ORA-01418, and also tried to drop index with correct owner and same result.

-- Steps To Reproduce:
Following parameters were used for expdp / impdp

Oracle 10.1.0.2.0
expdp userid=system/xxx DUMPFILE=TEXT_FULL.dmp DIRECTORY=DATA_PUMP2
SCHEMAS=USER_YP,CA2,PLUSDEMO,TEXT_USER EXCLUDE=STATISTICS

Oracle 10.2.0.4.0
impdp userid=system/xyz REMAP_SCHEMA=USER_YP:USER_TEXT
REMAP_TABLESPACE=TEXT:TEXT_YP DIRECTORY=DATA_PUMP2 DUMPFILE=TEXT_FULL.dmp
TABLE_EXISTS_ACTION=REPLACE

DataPump shows following similar errors during data load:

ORA-31693: Taulun tieto-objektin "USER_YP"."XML_DOCUMENT" lataus ei onnistunut. Syy ohitukseen on virhe:
ORA-00904: "SYS_NC00018$": virheellinen tunniste
ORA-31693: Taulun tieto-objektin "PLUSDEMO"."DOCUMENT" lataus ei onnistunut. Syy ohitukseen on virhe:
ORA-00904: "SYS_NC00018$": virheellinen tunniste
ORA-31693: Taulun tieto-objektin "TEXT_USER"."METADATA" lataus  ei onnistunut. Syy ohitukseen on virhe:
ORA-00904: "SYS_NC00018$": virheellinen tunniste


or

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 drsxsopen
ORA-00904: "BASE"."SYS_NC00019$"."GETCLOBVAL": invalid identifier

Failing sql is:
CREATE INDEX "TEXT_USER"."TEXT_METADATA_DOC_INDEX" ON "TEXT_USER"."METADATA" (SYS_MAKEXML("SYS_NC00009$"))  INDEXTYPE IS "CTXSYS"."CONTEXT" PARALLEL 1 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DE_S_TBL_IDX_DOMIDX_INDEX/INDEX
ORA-31684: Object type INDEX:"TEXT_USER"."TEXT_METADATA_DOC_INDEX" already exists

 

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