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 JUNE 13, 2023

Applies to:

Oracle Database - Standard Edition - Version 8.1.7.4 to 11.2.0.4 [Release 8.1.7 to 11.2]
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.4 [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
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

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.

Purpose

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_CHARACTERSET to 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.

Scope

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.

The NLS_CHARACTERSET is defining the characterset of the CHAR, VARCHAR2, LONG and CLOB datatypes.