Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)

Last updated on JUNE 13, 2023

Applies to:

Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.

Purpose

The purpose of this note is to provide the exact steps to change the current NLS_CHARACTERSET to AL32UTF8 / UTF8 or an other multibyte characterset.

The majority of SR's logged about "csalter not working" or "characterset change does not work" are simply due the steps in this note NOT been followed.
The note is rather long but this is to check for all known issues and to explain what objects need action so a correct conversion is possible.
Please DO follow the note and do not "skip" sections and use , when provided , the selects to have a clear idea what objects need action.
This note are "the exact steps".

In 10g and 11g you NEED to use Csalter (or the DMU tool).
In 12c you NEED to use the DMU tool.
Do NOT use "Alter database character set" in 10g, 11g or 12c to go to AL32UTF8 or UTF8.
Using "Alter database character set" to go to UTF8 or AL32UTF8 is NOT supported in 10g, 11g or 12c and WILL corrupt at least (!) Data Dictionary objects and most likely also User data.
If "Alter database character set" is used in 10g , 11g or 12c to go to AL32UTF8 or UTF8 the only action possible is back to backup.

From Oracle 12c onwards the DMU will be the only tool available to migrate to Unicode see <Note 1272374.1> The Database Migration Assistant for Unicode (DMU) Tool.

This note will only deal with the database (server side) change itself.
In 8i (8.1.7 and lower) you cannot use AL32UTF8 since this is not known, use UTF8 instead.

Note that in order to use an Unicode (AL32UTF8 / UTF8) database you need to make sure your application supports using an Unicode database. This is not something Oracle database support can "check" or "confirm".
Please consult the application documentation or ask the application vendor / support team if your application is certified to work with AL32UTF8 or UTF8 as NLS_CHARACTERSET.
For further implications on clients and application level when going to AL32UTF8 please see <Note 788156.1> AL32UTF8 / UTF8 (Unicode) Database Character Set Implications.

It's strongly recommended to:

do a complete "testdrive" of the WHOLE change upfront
read <Note 788156.1> AL32UTF8 / UTF8 (Unicode) Database Character Set Implications first and to make sure your application and clients are checked and ready for the change on database level.

The note can be used to go from any NLS_CHARACTERSET to AL32UTF8 / UTF8. ( which also means it can be used to go from UTF8 to AL32UTF8 (or inverse) ).

The note is written using AL32UTF8, to use this note to go to an other characterset (for example UTF8) simply replace "AL32UTF8" with "UTF8" in the CSSCAN TOCHAR and for 9i and lower in the alter database character set command.

This "flow" can also be used to go from any single byte characterset (like US7ASCII, WE8DEC) to any other Multi byte characterset (ZHS16GBK, ZHT16MSWIN950, ZHT16HKSCS, ZHT16HKSCS31,KO16MSWIN949, JA16SJIS ...), simply substitute AL32UTF8 with the xx16xxxx target characterset. But in that case going to AL32UTF8 would be simply be a far better idea. <Note 333489.1> Choosing a database character set means choosing Unicode.

Scope

Changing the current NLS_CHARACTERSET to AL32UTF8 / UTF8 or an other multibyte characterset.
In this note AL32UTF8 will be used, but it's applicable to UTF8 (like to be used in 8i instead of AL32UTF8) or other multibyte charactersets also.

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.