Tuesday, March 31, 2009

Export char semantic 10.2 database

Unable to export char semantic 10.2 database


Created a fresh database with NLS_LENGTH_SEMANTICS=CHAR. If you then run an export, then will get error like below
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


Here are the steps to “repair” my database

Step 1: 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:
SQL> shutdown immediate
SQL> startup migrate


Step 2: Now drop the XML DB stuff.
SQL> @?\RDBMS\ADMIN\catnoqm.sql

Note: You’ll have to close SQL*Plus at the end, then reopen again


Step 3: There is a bug that says you have to restart the database after running catnoqm. Be sure to start it with “startup migrate” again:
SQL> shutdown immediate
SQL> startup migrate


Step 4: 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


Step 5: 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
SQL> startup


After finishing the repair steps you can do export.

Find the meta link for above issue: Note 339938.1


Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode

No comments: