EXP-00056: 遇到 ORACLE 错误 932 ORA-00932: 数据类型不一致: 应为...
发布网友
发布时间：2022-04-27 05:03
共2个回答
热心网友
时间：2022-04-08 09:05
Full Export From 10.2.0.1 Aborts With EXP-56 ORA-932 (Inconsistent Datatypes) EXP-0 [ID 339938.1] Modified 30-MAR-2009 Type PROBLEM Status PUBLISHED In this Document
Symptoms
Cause
Solution
References
Applies to:Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
Oracle Server - Personal Edition - Version: 10.1.0.2 to 10.2.0.3
Oracle Server - Standard Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.
SymptomsA 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.CauseOne 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 CHARSolutionFor 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 \
mpfile=expdp_full.dmp logfile=expdp_full.log full=y
热心网友
时间：2022-04-08 10:23
使用Oracle导出工具exp导出全部数据时错误，如果使用单张表导出正常。
[oracle@oracle02 ~]$ exp userid=test/test file=test.dmp buffer=1024 owner=test
Export: Release 10.2.0.1.0 - Proction 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 Proction
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 proceral 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.
Procere created.
System altered.
System altered.
System altered.
PL/SQL procere successfully completed.
Procere dropped.
2.执行重新编译无效对象脚本 $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procere successfully completed.
PL/SQL procere successfully completed.
验证一下程序
SQL> select * from ku$_xmlschema_view;
V V OWNER_NUM OWNER_NAME URL
