Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET in 8i, 9i , 10g and 11g
(Doc ID 1283764.1)
Last updated on DECEMBER 22, 2019
Oracle Database - Standard Edition - Version 188.8.131.52 to 184.108.40.206 [Release 8.1.7 to 11.2] Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 8.1.7 to 11.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Information in this document applies to any platform.
***Checked for relevance on 01-Jul-2013***
NOTE: In the images and/or the document content below, the user information and environment data used
represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered
with an Oracle database product or other training material. Any similarity to actual environments,
actual persons, living or dead, is purely coincidental and not intended in any manner.
To provide a guide to change the NLS_CHARACTERSET from AL32UTF8 or UTF8 in 8i, 9i , 10g and 11g. This note will only deal with the database (server side) change itself.
This note cannot be used in 12c and higher, in 12c and higher the only option is to use expdp/impdp into a new database to migrate from AL32UTF8 to other charactersets. Refer 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)
Normally there is no reason to go FROM AL32UTF8 to another NLS_CHARACTERSET since AL32UTF8 can store all characters defined by all other character sets. If you created by mistake an (empty) AL32UTF8 database but you need another NLS_CHARACTERSET then you can also simple delete the database and recreate it with the NLS_CHARACTERSET you need. In the DBUA you can change the used NLS_CHARACTERSET in step 9 of 11 in the "character sets " tab. If there is already data in the AL32UTF8 database you can use this note.
If you want to go TO UTF8 or AL32UTF8 then please see <Note:260192.1> Changing the NLS_CHARACTERSETto AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
This note can be used to go from AL32UTF8 / UTF8 to any any single byte NLS_CHARACTERSET (like US7ASCII, WE8DEC , WE8ISO8859P1 etc etc) or any other Multi byte NLS_CHARACTERSET (ZHS16GBK, ZHT16MSWIN950, ZHT16HKSCS, ZHT16HKSCS31,KO16MSWIN949, JA16SJIS ...) in 8i, 9i , 10g and 11g.
The note is written using AL32UTF8 it can also be used to go from UTF8 simply replace "AL32UTF8" with "UTF8" in the note.
In 10g and 11g you NEED to use Csalter do NOT use "Alter database character set" in 10g and 11g.
Any DBA changing the current NLS_CHARACTERSET from AL32UTF8 / UTF8 to an other NLS_CHARACTERSET in 8i, 9i , 10g and 11g.
If you want to go to UTF8 or AL32UTF8 then please see <Note:260192.1> Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
The current NLS_CHARACTERSET is seen in NLS_DATABASE_PARAMETERS.
SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET' /
The NLS_CHARACTERSET is defining the characterset of the CHAR, VARCHAR2, LONG and CLOB datatypes.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!