During Import, Creating An Index On An Xml Table Fails With Ora-31000 (Doc ID 1262266.1)

Last updated on APRIL 22, 2019

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 "<index_name>" ON "<table_name>" (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 "<table_name>" 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
"<schema_name>.xsd" *** IN THE SOURCE DATABASE ***

*** IN THE TARGET DATABASE *** the XMLSCHEMA
"<schema_name>.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 <index_name>
ON <table_name> (
extractValue(
object_value,
'/SecListUpd/@UpdActn',
'xmlns="http://www.fixprotocol.org/FIXML-4-4"'
)
);

It executes successful after the import
This could be a workaround

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.