Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
(Doc ID 260192.1)
Last updated on APRIL 24, 2019
Oracle Database - Standard Edition - Version 184.108.40.206 to 220.127.116.11 [Release 8.0.3 to 11.2] Oracle Database - Enterprise Edition - Version 18.104.22.168 to 22.214.171.124 [Release 8.0.3 to 11.2] Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Information in this document applies to any platform.
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.
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.
Sqlplus / as sysdba 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!