10 Replies Latest reply: Nov 19, 2012 3:20 AM by Marco Gralike RSS

    export and import XMLType table

    970358
      Hi ,
      I want to export one table which contain xmltype column form oracle 11.2.0.1.0 and import into 11.2.0.2.0 version.

      I got following errors when i export the table , when i tried with exp and imp utility

      EXP-00107: Feature (BINARY XML) of column ZZZZ in table XXXX.YYYY is not supported. The table will not be exported.

      then i tried export and import pump.Exporting pump is working ,following is the log

      -----
      ;;;
      Export: Release 11.2.0.1.0 - Production on Wed Oct 17 17:53:41 2012

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
      ;;;
      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      ;;; Legacy Mode Active due to the following parameters:
      ;;; Legacy Mode Parameter: "log=<xxxxx>Oct17.log" Location: Command Line, Replaced with: "logfile=T<xxxxx>_Oct17.log"
      ;;; Legacy Mode has set reuse_dumpfiles=true parameter.
      Starting "<xxxxx>"."SYS_EXPORT_TABLE_01": <xxxxx>/******** DUMPFILE=<xxxxx>Oct172.dmp TABLES=<xxxxx>.<xxxxx> logfile=<xxxxx>Oct17.log reusedumpfiles=true
      Estimate in progress using BLOCKS method...
      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
      Total estimation using BLOCKS method: 13.23 GB
      Processing object type TABLE_EXPORT/TABLE/TABLE
      Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
      Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
      Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
      Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      . . exported "<xxxxx>"."<xxxxx>" 13.38 GB 223955 rows
      Master table "<xxxxx>"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
      ******************************************************************************
      Dump file set for <xxxxx>.SYS_EXPORT_TABLE_01 is:
      E:\ORACLEDB\ADMIN\LOCALORA11G\DPDUMP\<xxxxx>OCT172.DMP
      Job "<xxxxx>"."SYS_EXPORT_TABLE_01" successfully completed at 20:30:14
      ---------

      h4. I got error when i import the pump using following command

      +impdp sys_dba/***** dumpfile=XYZ_OCT17_2.DMP logfile=import_vmdb_XYZ_Oct17_2.log FROMUSER=XXXX TOUSER=YYYY CONTENT=DATA_ONLY TRANSFORM=oid:n TABLE_EXISTS_ACTION=append;+

      error is :

      h3. KUP-11007: conversion error loading table "CC_DBA"."XXXX"*
      h3. ORA-01403: no data found*

      h3. ORA-31693: Table data object "XXX_DBA"."XXXX" failed to load/unload and is being skipped due to error:*

      Please help me to get solution for this.
        • 1. Re: export and import XMLType table
          Marco Gralike
          Is the export done via SYS and the import done also with SYS ? Can you elaborate on this?
          Is the column (xmltype column) schemaless or defined with a XSD schema?

          What if you re-create the table with the xmltype column before hand in the 11.2.0.2.0 database and then import via the impdp utility, does that work?
          • 2. Re: export and import XMLType table
            970358
            Hi Marco Gralike,
            Thanks for your reply.
            h4. Is the export done via SYS and the import done also with SYS ? Can you elaborate on this?
            * Export is done with different shcema and import is done with "sys as sysdba"
            h4. Is the column (xmltype column) schemaless or defined with a XSD schema?
            * It is schemaless below is the script to create the table.

            CREATE TABLE trcb_xbrl(
            +"XBRLFILENAME" VARCHAR2(50 BYTE),+
            +"ARFILING_DATE" DATE,+
            +"FYEAR" VARCHAR2(4 BYTE),+
            +"FMONTH" VARCHAR2(2 BYTE),+
            +"FDAY" VARCHAR2(2 BYTE),+
            +"XBRLVERSION" VARCHAR2(10 BYTE),+
            +"XBRLFILE" "SYS"."XMLTYPE" ,+
            +"LMODIFIED_DATE" DATE,+
            +"FILINGOPTION" VARCHAR2(2 BYTE))+
            XMLTYPE COLUMN xbrlfile STORE AS BINARY XML
            ALLOW ANYSCHEMA
            ALLOW NONSCHEMA

            CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))

            h4. What if you re-create the table with the xmltype column before hand in the 11.2.0.2.0 database and then import via the impdp utility, does that work?
            * Still i didn't try this, but when i import using impdp , its creates the table successfully but it fails in importing the data.

            - thees_k
            • 3. Re: export and import XMLType table
              Marco Gralike
              Oracle XMLDB works under the covers, sometimes, with Oracle VPD (dbms_rls) functionality, that's why I asked to create the table first and attempt an import via data only.
              • 4. Re: export and import XMLType table
                Marco Gralike
                Do you were this
                CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))
                comes from...?

                SYS_MAKEXML is used by Oracle XMLDB for different purposes, internally, and although I figured out some use-cases, I wonder if this index was handmade or created by Oracle. Or is this "just" a copy/paste text of a tool like Toad. Has the XMLDB database bit (11.2.0.2.0) extended with the Oracle XBRL Extention functionality...?
                • 5. Re: export and import XMLType table
                  970358
                  CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))
                  above index is created by us because we are storing file which like
                  <?xml version="1.0" encoding="UTF-8"?>
                  <xbrl xmlns="http://www.xbrl.org/2003/instance" xmlns:AAAAAA="http://www.AAAAAA.COM/AAAAAA" xmlns:ddd4217="http://www.xbrl.org/2003/ddd4217" xmlns:link="http://www.xbrl.org/2003/linkbase" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                  <link:schemaRef xlink:href="http://www.fsm.AAAAAA.COM/AAAAAA-xbrl/v1/AAAAAA-taxonomy-2009-v2.11.xsd" xlink:type="simple" />
                  <context id="Company_Current_ForPeriod"> ...
                  I tried to export pump with and without using DATA_OPTIONS=XML_CLOBS too.Both time exporting was success but import get same KUP-11007: conversion error loading table "tab_owner"."bbbbb_XBRL" error.

                  I tried the import in different ways
                  1. Create table first then import data_only (CONTENT=DATA_ONLY)
                  2. Import all table

                  In both way table ddl is created successfully ,but it fail on import data.Following is the log when i importing
                  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
                  Master table "aaaaa"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
                  Starting "aaaaa"."SYS_IMPORT_TABLE_02":  aaaaa/********@vm_dba TABLES=tab_owner.bbbbb_XBRL dumpfile=bbbbb_XBRL_OCT17_2.DMP logfile=import_vmdb
                  bbbbb_XBRL_Oct29.log DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
                  Processing object type TABLE_EXPORT/TABLE/TABLE
                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                  *KUP-11007: conversion error loading table "tab_owner"."bbbbb_XBRL"*
                  *ORA-01403: no data found*
                  
                  ORA-31693: Table data object "tab_owner"."bbbbb_XBRL" failed to load/unload and is being skipped due to error:
                  ORA-29913: error in executing ODCIEXTTABLEFETCH callout
                  ORA-26062: Can not continue from previous errors.
                  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
                  Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                  Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
                  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
                  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                  Job "aaaaa"."SYS_IMPORT_TABLE_02" completed with 1 error(s) at 18:42:26
                  • 6. Re: export and import XMLType table
                    Marco Gralike
                    thees_k wrote:
                    CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))
                    above index is created by us because we are storing file which like
                    Even more curious. As mentioned SYS_MAKEXML is an Oracle "internal" function / undocumented. Were you advised by Oracle to do so?
                    Searching via Google...let me guess...you guys use Toad...

                    Maybe Mark's (mdrake) old but still valid example will work for you: Indexing a XMLType column record (aka recreate the index the proper way or temporary get rid of it before export/import)

                    Edited by: Marco Gralike on Oct 30, 2012 2:19 PM
                    • 7. Re: export and import XMLType table
                      970358
                      Hi Marco Gralike,
                      Thanks for your reply.As you suggested ,I have tried without index , it gives the same error.
                      In both case when i import pump (with index and without index) table was created successfully only fail on data part.

                      Regards,
                      Thees.
                      • 8. Re: export and import XMLType table
                        Marco Gralike
                        Create a SR, service request, on support.oracle.com, if you have not already done.
                        • 9. Re: export and import XMLType table
                          970358
                          Thanks Marco.
                          Source dump oracle is installed in windows 7 and destination oracl is linux. I suspect it might be the cause.I created SR with oracle.Thanks again.
                          • 10. Re: export and import XMLType table
                            Marco Gralike
                            Interested in what the outcome will be regarding the SR