解决了前一个EXP错误后 紧接着来了个
inconsistent datatypes: expected BLOB, CLOB got CHAR
的错误。
转载一下高人的解决方案：虽然是英文又很长 但是慢慢看 一步步来还是解决了我的问题
We’re in the middle of converting our 8.1.7.4 database to 10g release 2 (10.2). With this migration we’re also changing the character set of the database to AL32UTF8 and changing the database to char semantics. This gave us serious problems trying to export the database.
First a short introduction on the choice of character set and length semantics: Using Unicode as a character set will allow us to store any character and not just the western European characters we’re currently limited to. Unicode is a multi-byte character set which means that a single character could use more than a single byte. So what do you mean by VARCHAR2(10)? Is this 10 characters or 10 bytes? This used to be the same with our single byte character set, but these days are over. You can specify VARCHAR2(10 BYTE) or VARCHAR2(10 CHAR) if you want to be specific. However, all of our existing code doesn’t use this syntax. This is where the NLS_LENGTH_SEMANTICS parameter comes in. You can set this to BYTE or CHAR basically defining which one of the two is the default when not specifying it explicitly. A default installation will use BYTE semantics, but we decided to change it to CHAR. To me that is the more logical option.
So, we created a fresh database with NLS_LENGTH_SEMANTICS=CHAR. If you then run an export:
C:\>set ORACLE_SID=dev01 C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 C:\>exp '/ as sysdba' full=y file=full.dmp Export: Release 10.2.0.1.0 - Production on Tue Nov 22 09:48:06 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Personal Oracle Database 10g Release 10.2.0.1.0 With the Partitioning, OLAP and Data Mining options Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . 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
Searching MetaLink made me find Note 339938.1 which describes a situation where a 10.2 full export fails with an ORA-932 when exporting the cluster definitions. I figured I found the issue and a solution. But the note describes that some SYS objects should be invalid whereas all my SYS objects are valid. It also describes that a SELECT on sys.ku$_xmlschema_view should fail. This one does fail on me:
SQL> select url, local, stripped_val from sys.ku$_xmlschema_view;
select url, local, stripped_val from sys.ku$_xmlschema_view
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
Now it was time to open a Service Request (the new name for a TAR) on MetaLink. In the end the support engineer came with the solution. It all boiled down to two pieces of advice:
- only switch to char semantics after database creation (that has no impact on the “char semantics support”, any SYS objects cannot be CHAR and that is what happened here)
- use a “startup migrate” when running any patch or catalog scripts
So I shouldn’t have set NLS_LENGTH_SEMANTICS=CHAR before creating the database. The second advice to always use STARTUP MIGRATE is also a good tip. This will force the system of doing anything in byte semantics.
Here are the steps to “repair” my database. First check that the NLS_LENGTH_SEMANTICS is indeed set to CHAR. Then shutdown the database and restart as “startup migrate” You can see that the length semantics are now BYTE:
C:\>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 10:00:46 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Personal Oracle Database 10g Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> column parameter format a20 SQL> column value format a5 SQL> select * from nls_session_parameters 2 where PARAMETER='NLS_LENGTH_SEMANTICS'; PARAMETER VALUE -------------------- ----- NLS_LENGTH_SEMANTICS CHAR SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup migrate; ORACLE instance started. Total System Global Area 469762048 bytes Fixed Size 1249608 bytes Variable Size 146804408 bytes Database Buffers 314572800 bytes Redo Buffers 7135232 bytes Database mounted. Database opened. SQL> select * from nls_session_parameters 2 where PARAMETER='NLS_LENGTH_SEMANTICS'; PARAMETER VALUE -------------------- ----- NLS_LENGTH_SEMANTICS BYTE
Now drop the XML DB stuff. After that exit SQL*Plus. There is some bug that screws up your session. This means you’ll have to close SQL*Plus at the end:
SQL> @?\RDBMS\ADMIN\catnoqm.sql PL/SQL procedure successfully completed. User dropped. PL/SQL procedure successfully completed. Table dropped. SQL> exit ERROR: ORA-00600: internal error code, arguments: [729], [256], [space leak], [], [], [], [], [] Disconnected from Personal Oracle Database 10g Release 10.2.0.1.0 With the Partitioning, OLAP and Data Mining options (with complications)
There is a bug that says you have to restart the database after running catnoqm. Be sure to start it with “startup migrate” again:
C:\>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 10:05:17 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Personal Oracle Database 10g Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup migrate; ORACLE instance started. Total System Global Area 469762048 bytes Fixed Size 1249608 bytes Variable Size 146804408 bytes Database Buffers 314572800 bytes Redo Buffers 7135232 bytes Database mounted. Database opened. SQL> column parameter format a20 SQL> column value format a5 SQL> select * from nls_session_parameters 2 where PARAMETER='NLS_LENGTH_SEMANTICS'; PARAMETER VALUE -------------------- ----- NLS_LENGTH_SEMANTICS BYTE
Now run the CATQM.SQL script which requires three arguments (initial password and two tablespaces):
SQL> @?\RDBMS\ADMIN\catqm.sql change_on_install SYSAUX TEMP Table created. Table created. [[ lot of succesfull statements removed for readability ]] Package dropped. Package dropped. Function dropped. Commit complete.
Now shutdown and start the database normally. You can check that the length semantics are back to its original (CHAR) and the query from sys.ku$_xmlschema_view now works fine:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 469762048 bytes
Fixed Size 1249608 bytes
Variable Size 150998712 bytes
Database Buffers 310378496 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
2 where PARAMETER='NLS_LENGTH_SEMANTICS';
PARAMETER VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS CHAR
SQL> select url, local, stripped_val
2 from sys.ku$_xmlschema_view;
URL
----------------------------------------------------------------
LOCAL
----------
STRIPPED_VAL
----------------------------------------------------------------
http://xmlns.oracle.com/xdb/XDBSchema.xsd
0
<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:xdb="
http://xmlns.oracle.
http://xmlns.oracle.com/xdb/XDBResource.xsd
0
[[ lot of log removed for readability ]]
13 rows selected.
If you now retry the export it will succeed:
C:\>set ORACLE_SID=dev01 C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 C:\>exp '/ as sysdba' full=y file=full.dmp Export: Release 10.2.0.1.0 - Production on Tue Nov 22 10:23:09 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Personal Oracle Database 10g Release 10.2.0.1.0 With the Partitioning, OLAP and Data Mining options Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export SYSTEM's tables via Conventional Path ... . . exporting table DEF$_AQCALL 0 rows exported . . exporting table DEF$_AQERROR 0 rows exported . . exporting table DEF$_CALLDEST 0 rows exported . . exporting table DEF$_DEFAULTDEST 0 rows exported . . exporting table DEF$_DESTINATION 0 rows exported . . exporting table DEF$_ERROR 0 rows exported . . exporting table DEF$_LOB 0 rows exported . . exporting table DEF$_ORIGIN 0 rows exported . . exporting table DEF$_PROPAGATOR 0 rows exported . . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported [[ rest removed for readability ]]
The lessons learned from this experience:
原文地址：http://www.oratransplant.nl/2005/11/22/unable-to-export-char-semantic-102-database/
