[ORACLE-ORA]EXP-56 ORA-00932 (Inconsistent Datatypes) EXP ID 339938.1

renjixinchina發表於2012-12-28
Oracle

Applies to:

Oracle Server - Personal Edition - Version: 10.1.0.2 to 10.2.0.3 - Release: 10.1 to 10.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3   [Release: 10.1 to 10.2]
Oracle Server - Standard Edition - Version: 10.1.0.2 to 10.2.0.3   [Release: 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 14-SEP-2011***

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

One possible cause (note there might be others):

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-751808/，如需轉載，請註明出處，否則將追究法律責任。

相關文章