How to move XML schema based xmltype data from 10g to another schema/migrate to 11g (Doc ID 1318012.1)

Last updated on NOVEMBER 28, 2016

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 23-Dec-2013***

Symptoms

Customers who make use of schema based XMLType tables/columns in 10g need to have the capability to:


The following example demonstrates moving XML schema based data from 10.2.0.4 from user xdb11 to a 11.2.0.2 database to user xdb55.

Creating an xmltype column in 10.2.0.4:

SQL> connect xdb11/xdb11
Connected.

SQL> ! pwd
/bugmnt1/am/celcsol4/testcase

SQL> ! ls
po_bin.xml po_bin.xsd

SQL> create or replace directory XML_DIR as '/bugmnt1/am/celcsol4/testcase';

Directory created.

SQL> BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL =>'po_bin.xsd',
SCHEMADOC => bfilename('XML_DIR','po_bin.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => TRUE,
GENTYPES => FALSE,
GENTABLES => FALSE);
END;
/

PL/SQL procedure successfully completed.

SQL> CREATE TABLE po_bin_tab (key_column VARCHAR2(10), xml_column xmltype)
xmltype column xml_column store as object relational
xmlschema "po_bin.xsd"
element "PurchaseOrder";

Table created.


SQL> INSERT INTO po_bin_tab VALUES (101,
XMLType(BFILENAME('XML_DIR','po_bin.xml'),
NLS_CHARSET_ID('AL32UTF8')));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from po_bin_tab;

KEY_COLUMN
----------
XML_COLUMN
--------------------------------------------------------------------------------
101
<?xml version="1.0" encoding="US-ASCII"?>

<!--Sample XML file generated by XMLS


Attempting to use expdp on 10g, shows below that this is not possible. There is no support for XMLSchemas or XMLSchema-based columns prior to 11.x.

[celcsol4]/bugmnt1/am/celcsol4/testcase> expdp \'/ as sysdba\' directory=XML_DIR dumpfile=xdbtest.dmp schemas=XDB11

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 07 April, 2011 16:41:29

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": '/******** AS SYSDBA' directory=XML_DIR dumpfile=xdbtest.dmp schemas=XDB11
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"XDB11"."PurchaseOrder274_TAB" will be skipped.
ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"XDB11"."PO_BIN_TAB" will be skipped.
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/bugmnt1/am/celcsol4/testcase/xdbtest.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 16:44:12



It is necessary to firstly run a traditional export on 10g to ensure that all the objects contained in the dmp file will provide a means to move the schema based XMLType content:

[celcsol4]/bugmnt1/am/celcsol4/testcase> exp \'/ as sysdba\' file=xdb11.dmp owner=XDB11

Export: Release 10.2.0.4.0 - Production on Thu Apr 7 16:48:08 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user XDB11
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user XDB11
About to export XDB11's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export XDB11's tables via Conventional Path ...
. . exporting table PO_BIN_TAB 1 rows exported
. . exporting table PurchaseOrder274_TAB 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.



The following commands listed below took place on 11.2.0.2 database on another machine. Please note, for moving the export dump between machines and using ftp, be sure to FTP in binary mode.
The user xdb55 already exists on 11.2.0.2 prior to running the import.

The following example listed just below shows that using the toid_novalidate option which will precreate ALL tables, types with the OID's as per SOURCE db. Unfortunately though, this doesn't get the schema based XMLType objects moved and returns the error IMP-00086:

SQL> ! vi parfile.par
file=xdb11.dmp
fromuser=XDB11
touser=XDB55
rows=n
toid_novalidate=(
\"XDB55.ShippingInstructionsTyp264_T\",
\"XDB55.Action267_T\",
\"XDB55.Action268_COLL\",
\"XDB55.ActionsType266_T\",
\"XDB55.PartType269_T\",
\"XDB55.LineItemType270_T\",
\"XDB55.LineItem272_COLL\",
\"XDB55.LineItemsType271_T\",
\"XDB55.PurchaseOrderType273_T\",
\"XDB55.LineItemsType271_T\",
\"XDB55.ShippingInstructionsTyp264_T\",
\"XDB55.RejectionType265_T\",
\"XDB55.ActionsType266_T\",
\"XDB55.PurchaseOrderType273_T\",
\"XDB55.Action267_T\",
\"XDB55.Action268_COLL\",
\"XDB55.PartType269_T\",
\"XDB55.LineItemType270_T\",
\"XDB55.LineItem272_COLL\")

[oracle@caosspc46 /u02/testcase]$ imp \'/ as sysdba\' parfile=parfile.par

Import: Release 11.2.0.2.0 - Production on Sat Apr 9 11:24:02 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing XDB11's objects into XDB55
IMP-00086: TOID "ShippingInstructionsTyp264_T" not found in export file
IMP-00086: TOID "Action267_T" not found in export file
IMP-00086: TOID "Action268_COLL" not found in export file
IMP-00086: TOID "ActionsType266_T" not found in export file
IMP-00086: TOID "PartType269_T" not found in export file
IMP-00086: TOID "LineItemType270_T" not found in export file
IMP-00086: TOID "LineItem272_COLL" not found in export file
IMP-00086: TOID "LineItemsType271_T" not found in export file
IMP-00086: TOID "PurchaseOrderType273_T" not found in export file
IMP-00086: TOID "LineItemsType271_T" not found in export file
IMP-00086: TOID "ShippingInstructionsTyp264_T" not found in export file
IMP-00086: TOID "RejectionType265_T" not found in export file
IMP-00086: TOID "ActionsType266_T" not found in export file
IMP-00086: TOID "PurchaseOrderType273_T" not found in export file
IMP-00086: TOID "Action267_T" not found in export file
IMP-00086: TOID "Action268_COLL" not found in export file
IMP-00086: TOID "PartType269_T" not found in export file
IMP-00086: TOID "LineItemType270_T" not found in export file
IMP-00086: TOID "LineItem272_COLL" not found in export file
Import terminated successfully with warnings.

[oracle@caosspc46 /u02/testcase]$ sqlplus xdb55/xdb55

SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 9 11:25:36 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from user_objects;

COUNT(*)
----------
45

SQL> set pagesize 1000


SQL> desc po_bin_tab
Name                            Null?    Type
------------------------------- -------- ----------------------------------
KEY_COLUMN                               VARCHAR2(10)
XML_COLUMN                               SYS.XMLTYPE(XMLSchema "po_bin.xsd"
                                         Element "PurchaseOrder")
                                         STORAGE Object-relational
                                         TYPE "PurchaseOrderType273_T"

SQL> select count(*) from po_bin_tab;

COUNT(*)
----------
0

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