Database Character Set Healthcheck (Doc ID 225938.1)

Purpose

To give a overview of things you can check to debug "Lossy" data in a csscan output or to deduct what kind of encodings you have (incorrectly) stored in your database.

Scope

a) This is a rather "expert" level note. Please do NOT overrate your skills. If things are not clear, please log a SR, refer to this note and ask that someone from the RDBMS NLS "advanced resolution team" reviews your problem.

b) This note is the LAST step in debugging "display problems" like "I can't insert <your special character here> in a database". The FIRST step when having problem inserting NEW data is to see if your CURRENT database NLS_CHARACTERSET supports the character you want to insert.
The current NLS_CHARACTERSET is seen in NLS_DATABASE_PARAMETERS.

A very easy way to see if your current NLS_CHARACTERSET supports/defines the character you try to insert is to use an Unicode client like Oracle SQL Developer.
<note 1628060.1> How to diagnose losing characters , getting "funny" output when inserting or selecting other than A-Z,a-z data ( = non English data like Chinese, Russian, Hebrew , insert any language here to the list that is not English) CHAR, VARCHAR2, LONG or CLOB

Please start in above note to debug the problem.

c) If EXISTING data is NOT showing up correctly then check first if you can insert NEW data in a test table using SQLdeveloper. If this is possible then your current NLS_CHARTERSET defines this and EXISTING data cannot be seen in Sqldeveloper then you have (some) "garbage" data in your database and you can use this flow to try to rescue that data and AFTER that you can correct the "insert" (application) side.
If NEW and EXISITING data cannot be seen correctly seen in Sqldeveloper then this means the current NLS_CHARTERSET does NOT define this language and you can use this flow to correct the NLS_CHARACTERSET.

d) If following this flowchart is taking you further then step 2 you CANNOT go directly to another character set, even AL32UTF8. First the current *incorrect* setup needs to be corrected even if this means going first to an other character set.

e) If you are following this note because after a migration to a new version *without* changing the NLS_CHARACTERSET you have problems with some characters then please follow this note using you OLD NLS situation (=the NLS_LANG used on old clients before the migration to the new version of Oracle)

f) If following this note results in changing a database running on a Unix system to a "MSWIN" characterset then this is NOT a problem. You can use on Unix an database with a "Windows" characterset like WE8MSWIN1252. The only restriction is that you cannot use EBCDIC charactersets (like used on AS400 ea) on ASCII based platforms (like used on Unix and Windows) (or inverse) for the database characterset.

Details

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