Last updated on NOVEMBER 04, 2019
Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check. The national character set controls the encoding of data in NCHAR and NVARCHAR2 columns. If the database character set does not support Unicode, you may be able to store Unicode data in columns with these data types but that generally adds complexity to the system- applications may have to change to support the national character set. To check the character set of the Oracle DBMS, issue one of the following SQL queries using sqlplus or the Ingres SQL Terminal Monitor (isql) utility. (CSSCAN) utility helps you determine the impact and feasibility of migrating character data in an Oracle database to a new character set. The tool to change a PDB’s character set is the Data Migration Assistant for Unicode (DMU). DMU is installed in all Oracle releases since Oracle 11.2.0.4 by default. Just check the $ORACLEHOME/dmu subdirectory and start the DMU with sh dmu.sh. And find more information about the DMU here: DMU – Tips and Tricks. Use locale builder (from Oracle Database 9i onwards) to view what characters are defined for a particular Oracle character set. Choosing Unicode as the database character set ensures a strong foundation for whatever is built into and on top of the database. Oracle recommends using Unicode for all new system deployment.
Applies to:
Oracle Database - Standard Edition - Version 8.1.7.4 and laterOracle Database - Enterprise Edition - Version 8.1.7.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 09-Oct-2012***
*** Checked for relevance on 05-Apr-2016 ***
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
How To Check Character Set In Oracle Database
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
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 |
| References |
Check Oracle Database Character Set
In order to check what character set your Oracle installation is using you canrun the following query:
SELECT value$ FROM sys.props$ WHERE name ='NLS_CHARACTERSET';
In the screenshot below it can be seen that the character set in use isWE8MSWIN1252.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;
How To Check Oracle Database Character Set Utf8
How To Check Oracle Database Character Set
How To Check Nls Character Set In Oracle Database
Comments are closed.