During Import, Creating An Index On An Xml Table Fails With Ora-31000

Last updated on FEBRUARY 08, 2017

Applies to:

Symptoms

Using classic export utility. Exporting schema works ok. Importing the file into another database getting error:

IMP-00003: ORACLE error 31000 encountered
ORA-31000: Resource '%s' is not an XDB schema document

While the import tries to create this index:

CREATE INDEX "IX_RAW_FIXML_SECLISTUPD_UA" ON "RAW_FIXML_SECLISTUPD" (SYS_XM"
"LCONV("SYS_NC00037$",1,259,21612,'665A58132B082119E04009C039D93F7C',0,0,1)
"
") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 655360 FREELISTS 1 FR"
"EELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CONTRACT_DATA"
LOGGING"

1. The XMLType table "RAW_FIXML_SECLISTUPD" is created sucessfully.
2. The xml schema was also created/registered successfully.
3. The function SYS_XMLCONV has as fifth parameter the xml schema OID
which as you can see is '665A58132B082119E04009C039D93F7C'

That is the OID for the XMLSCHEMA
"fixml-securitystatus-impl-4-4-FIA-1-1.xsd" *** IN THE SOURCE DATABASE ***

*** IN THE TARGET DATABASE *** the XMLSCHEMA
"fixml-securitystatus-impl-4-4-FIA-1-1.xsd"
has OID '7093E18DB65D6F2AE04009C010D41FB5'

As the OID in the target is different than the OID in the source, when
the functional index is created by the import using the SYS_XMLCONV
which uses different OID, Oracle generates error:

ORA-31000: Resource '%s' is not an XDB schema document

The original CREATE INDEX statement is:

CREATE INDEX IX_INDEX
ON XML_TABLE (
extractValue(
object_value,
'/SecListUpd/@UpdActn',
'xmlns="http://www.fixprotocol.org/FIXML-4-4"'
)
);

It executes successful after the import
This could be a workaround

Cause