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
0MetaLink Note 144808.1 for more information and limits of using BYTE and CHAR semantics. This case will be added to this Note. Update 17-dec-2005: Oracle filed bug 4886376 to have this problem fixed in a future release. The bug description also indicates that AL32UTF8 will be the default character set for a version 11.x database.
