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

Problem Description:
Database character set change is erroring out with following error message.
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Solution Description:
Use INTERNAL_USE option along with the above command. Here is the steps to change the character set. 

conn / as sysdba
startup restrict;
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
shutdown immediate;
conn / as sysdba
startup

Example:

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup restrict;
ORACLE instance started.

Total System Global Area 1.0536E+10 bytes
Fixed Size                  2935416 bytes
Variable Size            1.0435E+10 bytes
Database Buffers           67108864 bytes
Redo Buffers               30617600 bytes
Database mounted.
Database opened.

SELECT * FROM NLS_DATABASE_PARAMETERS 
where parameter='NLS_CHARACTERSET';
PARAMETER                                VALUE                         ---------------------------------------- ------------------------------   
NLS_CHARACTERSET                         WE8MSWIN1252

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1.0536E+10 bytes
Fixed Size                  2935416 bytes
Variable Size            1.0435E+10 bytes
Database Buffers           67108864 bytes
Redo Buffers               30617600 bytes
Database mounted.
Database opened.

SELECT * FROM NLS_DATABASE_PARAMETERS 
where parameter='NLS_CHARACTERSET';
PARAMETER                                VALUE                                                     
---------------------------------------- ------------------------------   
NLS_CHARACTERSET                         AL32UTF8
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google