Changing US7ASCII or WE8ISO8859P1 to WE8MSWIN1252 in 8i, 9i, 10g and 11g (Doc ID 555823.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.4 [Release 8.1.7 to 11.2]
Oracle Database - Standard 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.
***Checked for relevance on 12-Mar-2018***

Purpose

To provide a guide to change the NLS_CHARACTERSET from US7ASCII or WE8ISO8859P1 to WE8MSWIN1252.

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

The current NLS_CHARACTERSET is seen in NLS_DATABASE_PARAMETERS.

conn / as sysdba
select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

This note is ONLY for 8i, 9i, 10g and 11g it cannot be used for Oracle RDBMS 12c
For other characterset conversions and Oracle 12c please see <Note 225912.1> Changing the Database Character Set ( NLS_CHARACTERSET )

Scope

Any DBA want to change the current NLS_CHARACTERSET from US7ASCII or WE8ISO8859P1 to WE8MSWIN1252 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

1) Prerequisites

US7ASCII versus WE8MSWIN1252

WE8ISO8859P1 versus WE8MSWIN1252

2) Check the source database for:

2.a) Invalid objects.

2.b) Orphaned Datapump master 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 WE8MSWIN1252

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 WE8MSWIN1252 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 WE8MSWIN1252->WE8ISO8859P1 or WE8MSWIN1252 -> US7ASCII ?

9b) Are you sure I can create a "MSWIN" database on a Unix platform?

9c) why is there no data loss when moving data from an WE8ISO8859P1 to another WE8ISO8859P1 db (or from an US7ASCII to another US7ASCII db) if if they contain"lossy" characters when using export/import or dblinks and why should I not keep using this?

9d) WE8MSWIN1252 is superset of WE8ISO8859P1 and US7ASCII , so why is there any data loss when exporting/importing data from an US7ASCII/WE8ISO8859P1 db containing WE8MSWIN1252 codes into a WE8MSWIN1252 database?

9e) But if I import a dataset from a WE8ISO8859P1 db that contains characters like the € (which is not defined in WE8ISO8859P1) into an AL32UTF8 db, I still see the € when selecting the data from my old application.

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