My Oracle Support Banner

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

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

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.

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 from AL32UTF8 to an other NLS_CHARACTERSET
 1.a) Prerequisites:
 1.b) When changing an Oracle Applications 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?
 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's/users.
 2.d) Make sure your database is in good health.
 3)  Check the AL32UTF8 database for "Lossy" (invalid code points in the current source character set).
 4) Check for "Convertible" and "Lossy" data when going to the new NLS_CHARACTERSET
 5) Dealing with "Lossy" 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:
 7.b) Functional indexes on CHAR semantics columns.
 7.c) SYSTIMESTAMP in the DEFAULT value clause for tables using CHAR semantics.
 7.d) Clusters using CHAR semantics.
 7.e) Unused columns using CHAR semantics
 7.f) Objects in the recyclebin (10g and up)
 7.g) 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 
 8) After any "Lossy" is solved and "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).
 8.b) For 10g and up the Csscan output needs to be
 9) Summary of steps needed to use Alter Database Character Set / Csalter:
 9.a) For 9i and lower:
 9.b) For 10g and up:
 10) Running Csalter/Alter Database Character Set
 10.a) For 8i/9i
 10.b) For 10g and up
 11) Reload the data pump packages after a change from AL32UTF8 in 10g and up.
 12) Import the exported data .
 12.a) When using Csalter/Alter database to go from AL32UTF8 to <NEW NLS_CHARACTERSET> and there was no "Convertible" in the csscan done in point 4:
 12.b) When using Csalter/Alter database to go from AL32UTF8 to <NEW NLS_CHARACTERSET> and there was "Convertible" in the csscan done in point 4:
 12.c) When using full export/import to go from AL32UTF8 to <NEW NLS_CHARACTERSET>
 13) Check your data
References

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