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
