exp/imp 操作过程中遇到932错误

今天做了一个简单的用户模式的exp导出，结果出了exp-00056错误和ora-00932错误。第一次实验，查了一下，解决的办法很简单，我慢慢说下思路。

我是想导出一个用户模式的信息，样的话会陆续导出了表的一些同义词，这个用户的视图，生么触发器，过程等等等跟这个用户相关的信息。但是出现了这个错误：

[oracle@oracle02 ~]$ exp userid=test/test file=test.dmp buffer=1024 owner=test

Export: Release 10.2.0.1.0 - Production on Tue Nov 28 15:09:42 2006

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

原因：数据字典中有存在一些无效KU$_视图；
SQL> select * from sys.ku$_xmlschema_view;
select * from sys.ku$_xmlschema_view
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

解决办法：
1.执行对象视图定义脚本 $ORACLE_HOME/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/catmetx.sql

Package altered.
Index altered.
View created.
Grant succeeded.
View created.
Grant succeeded.
Procedure created.
System altered.
System altered.
System altered.
PL/SQL procedure successfully completed.
Procedure dropped.
------开始我用一个普通用户执行的这个，出现了一些错误，报什么某个表不存在，之后换了sys用户执行的，执行完了就一切正常了。。

2.执行重新编译无效对象脚本 $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
验证一下程序
SQL> select * from ku$_xmlschema_view;

V V OWNER_NUM OWNER_NAME URL
- - ---------- ------------------------------ ---------------
1 0 46 MDSYS http://www.opengis.net/gml/geometry.xsd
1 0 46 MDSYS http://www.w3.org/1999/xlink/xlinks.xsd
1 0 46 MDSYS http://www.opengis.net/gml/feature.xsd
重新执行导出程序exp，一些正常。

Applies to: 
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1
Oracle Server - Personal Edition - Version: 10.1.0.2 to 10.2.0.1
Oracle Server - Standard Edition - Version: 10.1.0.2 to 10.2.0.1
This problem can occur on any platform.

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
References
Bug 4668539 - (BUG)ORA-932 WHILE EXPORT
Bug 4685068 - EXPORT IS FAILING WITH ERROR ORA-00932 ON 10.2

Keywords
CATPROC.SQL ; INVALID~OBJECT ; BLOB ; XDB ; CLOB ; FULL~DATABASE~EXPORT ; DBMS_METADATA ;  

上面的是 10.2.0.1的文档，标准的解决办法。

下面是转自晶晶小妹的一篇文章。或许以后能用到：

一个简单的EXP-00056：

EXP-00056吗，这是一个很普通的错误，通常是由于EXP程序在执行某条命令错误所造成的，可以通过自己手动执行如下脚本解决，

@?/rdbms/admin/catexp.sql

这种问题一般在升级过数据库后有可能出现，我以前也遇到过一次。于是，按照惯性思维，先执行第一个脚本，再EXP，错误依旧，再执行第二个脚本，同样不行。这可怎么办，忽然注意到EXP第一行显示的信息：Export: Release 10.2.0.1.0 - Production 。
数据库的版本是9i，而EXP确是10G，测试机上装了两个数据库，先装9i，后装的10G，PATH环境参数被设置为相关10G的，执行EXP时，并没有将环境参数改为9i的，设置一下PATH参数，或者进入到9i的文件夹下：cd Eracleora92in ，然后在执行EXP：
Eracleora92in>exp scott/ifclwx
一切顺利了。
如果你的测试机中9i和10g并存，就要注意这个问题了。