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开发者_JAVA百科 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.

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.

The Easiest way: (Shutdown neccesary):

First, Connect as sysdba:

sqplus / as sysdba

Next, execute the following script:

alter system set nls_length_semantics=CHAR scope=both;
shutdown;
startup restrict;
alter database character set INTERNAL_USE WE8ISO8859P1;
shutdown;
startup;

It worked for me in a Oracle 12c Standard Two Edition

Taken from: https://www.elblogdelpibe.com/2015/05/como-solucionar-el-error-ora-12899.html (updated URL)

replace line 6 by

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

this solved my problem.

Below worked for me in 19C.

Issue

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set Warning: possible data loss in character set conversions

Steps

Database started in Restriction mode.

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER

VALUE

NLS_CHARACTERSET AL32UTF8

SQL> alter database character set WE8MSWIN1252; alter database character set WE8MSWIN1252 * ERROR at line 1: ORA-12712: new character set must be a superset of old character set

SQL> alter database character set INTERNAL_USE WE8MSWIN1252;

Database altered.

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER

VALUE

NLS_CHARACTERSET WE8MSWIN1252

SQL>

Cheers! RaJ...