My Oracle Support Banner

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

Last updated on APRIL 24, 2019

Applies to:

Oracle Database - Standard Edition - Version 8.0.3.0 to 11.2.0.4 [Release 8.0.3 to 11.2]
Oracle Database - Enterprise Edition - Version 8.0.3.0 to 11.2.0.4 [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.

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:

 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.

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.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 1) General remarks on going to AL32UTF8
 1.a) Prerequisites:
 1.b) When changing an Oracle Applications Database or Peoplesoft database:
 1.c) When to use (full) export / import and when to use Alter Database Character Set / Csalter?
 1.d) When using Expdp/Impdp (DataPump)
 1.e) Using Alter Database Character Set on 9i
 1.f) What about Physical / Logical Standby databases?
 1.G) How to test the steps in this note upfront?
 2) Check the source database for unneeded or problematic objects:
 2.a) Invalid objects.
 2.b) Orphaned Datapump master tables (10g and up)
 2.c) Unneeded sample schema/users.
 2.d) Make sure your database is in good health.
 3) Check the Source database for "Lossy" (invalid code points in the current source character set).
 3.a) How to "rescue" lossy data?
 3.b) Can I solve "lossy" user/application data in an other way?
 4) Check for "Convertible" and "Truncation" data when going to AL32UTF8
 5) Dealing with "Truncation" data.
 5a) or shorten the data before export
 5b) or adapt the columns to fit the expansion of the data
 6) Dealing with "Convertible" data.
 6.a) "Convertible" Application Data:
 6.b) "Convertible" data in Data Dictionary objects:
 7) Before using Csalter / Alter Database Character Set check the database for:
 7.a) Partitions using CHAR semantics - ORA-14265: data type or length of a table subpartitioning column may not be changed" or " ORA-14060: data type or length of a table partitioning column may not be changed" during the change to AL32UTF8
 7.b) Function , Domain or Joined indexes on CHAR semantics columns.
 7.b.1) Functional or domain indexes on columns using CHAR semantics - "ORA-30556: functional index is defined on the column to be modified" or with "ORA-02262: ORA-904 occurs while type-checking column default value expression" during the change to AL32UTF8
 7.b.2) Join indexes on columns using CHAR semantics - "ORA-54028: cannot change the HIDDEN/VISIBLE property of a virtual column" during the change to AL32UTF8
 7.c) SYSTIMESTAMP in the DEFAULT value clause for tables using CHAR semantics - " ORA-604 error occurred at recursive SQL level %s , ORA-1866 the datetime class is invalid" during the change to AL32UTF8 .
 7.d) Clusters using CHAR semantics - "ORA-01447: ALTER TABLE does not operate on clustered columns" during the change to AL32UTF8
 7.e) Unused columns using CHAR semantics - "ORA-00604: error occurred at recursive SQL level 1" together with an "ORA-00904: "SYS_C00002_09031813:50:03$": invalid identifier" during the change to AL32UTF8
 7.f) Check that you have enough room to run Csalter or to import the "Convertible" data again afterwards.
 7.g) (10g and 11g) Objects in the recyclebin - "ORA-38301 can not perform DDL/DML over objects in Recycle Bin" during the change to AL32UTF8
 7.h) Check if the compatible parameter is set to your base version
 7.i) for Oracle 11.2.0.3 , 11.2.0.2, 11.2.0.1 , 11.1.0.7 and 11.1.0.6  : check for SQL plan baselines and profiles
 7.j) Leftover Temporary tables using CHAR semantics - " ORA-14450: attempt to access a transactional temp table already in use" during the change to AL32UTF8
 8) After any "Lossy" is solved, "Truncation" data is planned to be addressed and/or "Convertible" exported / truncated / addressed and point 7) is ok run Csscan again as final check.
 8.a) For 8i/9i the Csscan output needs to be "Changeless" for all CHAR, VARCHAR2, CLOB and LONG data (Data Dictionary and User/Application data) ( = BEFORE going to point 10.a) ).
 8.b) For 10g and 11g the Csscan output needs to be ( = BEFORE going to point 10.b) )
 9) Summary of steps needed to use Alter Database Character Set / Csalter:
 9.a) For 9i and lower:
 9.b) For 10g and 11g:
 10) Running Csalter ( 10g and 11g) or Alter Database Character Set (8i and 9i)
 10.a) The steps for 8i and 9i ONLY - do NOT use these in 10g or 11g
 10.b) The steps for version 10g  and 11g
 10.c) Check the Csalter/alter database output and the alert.log for errors, some Csalter messages do NOT have an ORA- number.
 11) (10g and 11g ) Reload the data pump packages after a change to AL32UTF8 in 10g and up.
 12) Import the exported data back into the database.
 12.a) When using Csalter/Alter database to go to AL32UTF8 and there was "Truncation" data in the csscan done in point 4:
 12.b) When using (Full) export/import to go to a new/other AL32UTF8 database and there was "Truncation" data in the csscan done in point 4:
 12.c) When using Csalter/Alter database to go to AL32UTF8 and there was NO "Truncation" data, only "Convertible" and "Changeless" in the csscan done in point 4:
 12.d) When using (full) export/import to go to a new/other AL32UTF8 database and there was NO "Truncation" data, only "Convertible" and "Changeless" in the csscan done in point 4:
 13) Check your data and final things:
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.