3 Replies Latest reply: Jul 13, 2011 9:23 AM by scanbix RSS

    Can't export my database using exp with XMLTYPE member column

    597647
      Hi,

      I have a database that has several tables in it. Some of the tables contain CLOBS, while others contain both CLOBs and XMLTypes. I have also installed XMLDB. After installing XMLDB, I am unable to utilize the export 'exp' utility to export data out of the database.

      I am running Oracle 10gR2

      If I try to use exp, I get the following:

      EXP-00056: ORACLE error 932 encountered
      ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
      EXP-00000: Export terminated unsuccessfully


      I read that prior to 11g, exporting data using the datapump 'expdp' will not work.

      --> Exporting XMLType Table in Table mode  (using Oracle exp)

      Has anyone else had problems using 'exp' with your database and xmldb? If so, please respond with your comments regarding how you were able to get around the issue.

      Many thanks in advance for whatever feedback you can provide!
        • 1. Re: Can't export my database using exp with XMLTYPE member column
          597647
          Allow me to update my original post...

          I was able to export the database with the XMLTYPE column present. However, I cannot export the database after loading the xml database. Here are the steps I am taking to create the XMLDB:

          create tablespace XML_DATA
          @$ORACLE_HOME/rdbms/admin/catqm.sql <xdb password> XML_DATA TEMP
          @$ORACLE_HOME/rdbms/admin/catxdbj.sql
          alter user xdb identified by <xdb password> account unlock;
          add a single XML DTD file to the root of the repository.

          Once this is complete, I can not longer export my database.

          If I just use the XMLTYPE datatype as one of my columns (which I store as a CLOB) without installing XDB, then I can't write to the XMLTYPE column using a java jdbc connection. I get the error:

          ORA-21700: object do
          es not exist or is marked for delete
          ORA-06512: at "SYS.XMLTYPE", line 254
          ORA-06512: at line 1

          java.sql.SQLException: ORA-21700: object does not exist or is marked for delete
          ORA-06512: at "SYS.XMLTYPE", line 254
          ORA-06512: at line 1

          Again, Oracle 10gR2.
          • 2. Re: Can't export my database using exp with XMLTYPE member column
            597647
            It feels funny replying to my own posts, but I'm making some progress so I feel the need to post the information.

            I came across the following article in metalink:

            Symptoms
            A full database export from a Oracle10g database aborts with:

            ...
            . exporting cluster definitions
            EXP-00056: ORACLE error 932 encountered
            ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
            EXP-00056: ORACLE error 932 encountered
            ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
            EXP-00000: Export terminated unsuccessfully
            EXP-00000: Export terminated unsuccessfully

            If export was started with SYS schema, a table level export may also fail with:

            ...
            Current user changed to TEST
            . . exporting table DOC_ARCHIVE 16 rows exported
            Current user changed to SYS
            EXP-00011: SYS.; does not exist
            Export terminated successfully with warnings.

            Cause
            Script $ORACLE_HOME/rdbms/admin/catmeta.sql has been run recently.

            There are several invalid SYS.KU$_% views in the dictionary:

            -- invalid objects:
            SET lines 120 pages 2000
            COL status FOR a9
            COL object_type FOR a20;
            COL owner.object FOR a50
            SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
            FROM dba_objects
            WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
            ORDER BY 4,2;

            STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
            --------- ---------- --------------- --------------------------------
            INVALID 7105 PACKAGE BODY SYS.DBMS_METADATA
            INVALID 6683 VIEW SYS.KU$_10_1_COMMENT_VIEW
            INVALID 6788 VIEW SYS.KU$_10_1_IND_STATS_VIEW
            INVALID 6778 VIEW SYS.KU$_10_1_PIND_STATS_VIEW
            INVALID 6752 VIEW SYS.KU$_10_1_PTAB_STATS_VIEW
            INVALID 6770 VIEW SYS.KU$_10_1_SPIND_STATS_VIEW
            INVALID 6748 VIEW SYS.KU$_10_1_TAB_ONLY_STATS_VIEW
            ... (etc)

            A query in SQL*Plus on sys.ku$_xmlschema_view also fails with ORA-932:

            SET lines 200 pages 2000
            COL url FOR a60 WRA
            SELECT url, local, stripped_val
            FROM sys.ku$_xmlschema_view;

            ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

            Solution
            For reasons having to do with compatibility, the XDB objects cannot be created by the catproc.sql script. The script catproc.sql therefore calls the catmeta.sql script, which contains fake object views for XDB objects.
            The real object views are defined in the catmetx.sql script (this script is invoked by catxdbv.sql which is invoked by catqm.sql).

            Solution #1
            Run following scripts while connected as SYS user:
            sqlplus /nolog
            SQL> connect / as sysdba
            SQL> @?/rdbms/admin/catmetx.sql
            SQL> @?/rdbms/admin/utlrp.sql

            SQL> exit

            Afterwards, re-run the export.

            or:

            Solution #2
            Run the export with the Export DataPump client. E.g.:
            expdp system/manager directory=my_dir \
            dumpfile=expdp_full.dmp logfile=expdp_full.log full=y



            After performing those steps I was able to use the DataPump to export the database. However, I still could not use 'exp'.

            The article above mentions 'catproc' which I'm not running as part of installing the XDB schema... I only run catproc after I initally create my database. Should I be running it along with the other scripts for XDB?
            • 3. Re: Can't export my database using exp with XMLTYPE member column
              scanbix
              Solution #1 worked on 10g R2