Oracle – SQL Error: ORA-12712: new character set must be a superset of old character set

oracleutf-8

I want to change character set of oracle database from 'WE8MSWIN1252' to 'AL32UTF8'

I tried to execute following steps from the link (http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch10.htm#1009580):

Shut down the database, using either a SHUTDOWN IMMEDIATE or a
SHUTDOWN NORMAL statement. Do a full backup of the database because
the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:

STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET AL32UTF8;

But when i execute the above statement, I am getting the following error

SQL Error: ORA-12712: new character set must be a superset of old character set

Can anyone please help me in resolving this issue.

Best Answer

For an ALTER DATABASE CHARACTER SET statement to execute successfully, two conditions must be fulfilled:

Each and every character in the current character set is available in the new character set.
Each and every character in the current character set has the same code point value in the new character set. (ie: the old charset must be a subset of the new one)

Because WE8MSWIN1252 is not a strict subset of AL32UTF8 this statement will fail (example: the pound sign is A3 in hex in WE8MSWIN1252, but in AL32UTF8 it is C2 A3).

You'll need to use CSALTER to do this migration.

Refer to: Character Set Migration.

Related Solutions

Sql – ORA-00980 error when attempting export using the EXP command

This can happen if the JVM installation is corrupted. Try:

SELECT comp_id, schema, status, version, comp_name
  FROM dba_registry
 ORDER BY 1;

If this returns a row with the comp_id of JAVAVM with a status of 'INVALID' you'll need to reinstall the java VM.

Metalink document 276554.1 has the procedure for doing so. If you can recover easily, it might be easier to recreate the database and reload it.

EDIT: I found Oracle-base link where a poster claims this will uninstall and reinstall the JVM (on Unix), I presume it works on windows with slight mods:

(WARNING! You can seriously hose your database if things go wrong here. BACKUP first!)

cd $ORACLE_HOME/javavm/install
sqlplus / as sysdba
@rmjvm.sql
@initjvm.sql

-- Recompile invalid objects
@?/rdbms/admin/utlrp

The metalink note is quite a bit more involved.

Oracle – Loading Unicode Characters with Oracle SQL Loader (sqlldr) results in question marks

You have two problem;

Character set.

Answer: You can solve this problem by finding your text character set (most of time notepad++ can do this.). After finding character set, you have to find sqlldr correspond of character set name. So, you can find this info from link https://docs.oracle.com/cd/B10501_01/server.920/a96529/appa.htm#975313 After all of these, you should solve character set problem.

In contrast to your actual data length, sqlldr says that, Field in data file exceeds maximum length.

Answer: You can solve this problem by adding CHAR(4000) (or what the actual length is) to problematic column. In my case, the problematic column is "E" column. Example is below. In my case I solved my problem in this way, hope helps. LOAD DATA CHARACTERSET UTF8 -- This line is comment -- Turkish charset (for ÜĞİŞ etc.) -- CHARACTERSET WE8ISO8859P9 -- Character list is here. -- https://docs.oracle.com/cd/B10501_01/server.920/a96529/appa.htm#975313 INFILE 'data.txt' "STR '~|~\n'" TRUNCATE INTO TABLE SILTAB FIELDS TERMINATED BY '#' TRAILING NULLCOLS ( a, b, c, d, e CHAR(4000) )

Best Answer

Related Solutions

Sql – ORA-00980 error when attempting export using the EXP command

Oracle – Loading Unicode Characters with Oracle SQL Loader (sqlldr) results in question marks

Related Topic