Cannot Export XML Schema-Based Table From 11g For Import To 10g (Doc ID 1307634.1)

Last updated on MARCH 21, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
Checked for Relevance on 22-Mar-2013

Symptoms

User SCOTT has an XML schema-based XMLType table and another table that has an XML-schema based xmltype column created as below in the 11.2.x database:

create table my_xmltype_col (
  id number,
  doc sys.XMLTYPE)
xmltype column doc
xmlschema "http://my_po.xsd" element "purchaseOrder";

create table my_xmltype_tab of xmltype
xmltype xmlschema "http://my_po.xsd" element "purchaseOrder";


In order to export these tables through expdp, and ensure that the dump file generated is readable by a 10g database, the parameter "VERSION=10.2.0" must be used, e.g.

expdp system/manager SCHEMAS=scott VERSION=10.2.0 DIRECTORY=dumpdir DUMPFILE=expdp_scott.dmp


However, running expdp from 11g with "VERSION=10.2.0" skips the SCOTT.my_xmltype_col and SCOTT.my_xmltype_tab tables:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" schemas=SCOTT version=10.2.0 directory=dumpdir dumpfile=expdp_scott.dmp logfile=expdp_scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
...
ORA-39139: Data Pump does not support XMLType objects in version 10.2.0. TABLE_DATA:"SCOTT"."MY_XMLTYPE_COL" will be skipped.
...
ORA-39139: Data Pump does not support XMLType objects in version 10.2.0. TABLE_DATA:"SCOTT"."MY_XMLTYPE_TAB" will be skipped.
...


Another way to generate a dump file from an 11g database that is readable by a 10g database is to use 10g's exp utility, e.g.

<10G_ORACLE_HOME>/bin/exp system/manager@my11gdb OWNER=scott file=exp_scott.dmp log=exp_scott.log


However, using 10g's exp also fails to export SCOTT.my_xmltype_col and SCOTT.my_xmltype_tab tables:

Export: Release 10.2.0.4.0 - Production on Fri Mar 25 16:08:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
<snip>
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table MY_XMLTYPE_COL
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 26:
PLS-00306: wrong number or types of arguments in call to 'XMLSCHEMAGETDEPENDENCYLIST'
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
<snip>
. . exporting table MY_XMLTYPE_TAB
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 26:
PLS-00306: wrong number or types of arguments in call to 'XMLSCHEMAGETDEPENDENCYLIST'
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
<snip>
. exporting synonyms

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