My Oracle Support Banner

Changing US7ASCII TO WE8ISO8859P1 in 8i, 9i, 10g and 11g (Doc ID 1963942.1)

Last updated on JANUARY 30, 2022

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
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]
Information in this document applies to any platform.

Purpose

To provide a guide to change the NLS_CHARACTERSET from US7ASCII to WE8ISO8859P1

We strongly advice to follow this note also when using export/import from an US7ASCI to a WE8ISO8859P1 database.

The current NLS_CHARACTERSET is seen in NLS_DATABASE_PARAMETERS.

 

Scope

Any DBA wanting to change the current NLS_CHARACTERSET from US7ASCII to WE8ISO8859P1 in 8i, 9i, 10g and 11g.

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) Prerequisites
 US7ASCII versus WE8ISO8859P1
 2) Check the source database for:
 2.a) Invalid objects.
 2.b) Orphaned Datapump primary tables (10g and up)
 2.c) Unneeded sample schema's/users.
 2.d) Objects in the recyclebin (10g an up)
 3) Check if there are no invalid code points in the database for the current NLS_CHARACTERSET:
 4) Csscan lists "Lossy" data in the scan performed in step 3.
 5) Final Csscan run when going to WE8ISO8859P1
 5.a)  If there IS NO "Lossy" data in point 3 then perform a last check with csscan :
 5.b) If there IS "lossy" data in point 3 and you are sure the "Lossy" data is actual WE8ISO8859P1 data then perform a last check with csscan :
 5.c) Both scans will will create 3 files :
 5.c.1) The needed csscan output for 8i/9i  to use "Alter Database Character Set".
 5.c.2) The needed csscan output for 10g and up to use Csalter.
 6) Performing the actual character set change:
 6.a) For 9i and 8i
 6.b) For 10g and up:
 7) Make sure clients are using the correct NLS_LANG setting:
 7a) Microsoft Windows clients
 7b) Unix clients
 7c) Other clients:
 8) What about Physical / Logical Standby databases?
 9) Other Often Asked Questions:
 9a) Can I go from WE8ISO8859P1 -> US7ASCII ?
References

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