View and set the Oracle database character set

The database server character Set select * from Nls_database_parameters, which is derived from props$, is the character set representing the database. 
　　
Client Character Set environment select * from Nls_instance_parameters, which originates from V$parameter,
　　
represents the setting of the client's character set, which may be a parameter file, an environment variable, or a registry
　　
Session Character Set environment select * from Nls_session_parameters, which originates from V$nls_parameters, represents the session's own settings, which may be the session's environment variable or alter session completion, If the session does not have a special setting, it will be consistent with nls_instance_parameters. 
　　
the client's character set requires consistency with the server to correctly display non-ASCII characters for the database. If multiple settings exist, ALTER session> environment variable > Registry > Parameter File
　　
Character set requirements are consistent, but language settings can be different, language settings are recommended in English. If the character set is ZHS16GBK, then Nls_lang can be AMERICAN_AMERICA.ZHS16GBK. 
 
involves a three-part character set,
1. Oracel server-side character set;
2. The character set of the Oracle client side;
3. The character set of the DMP file. 
 
When doing data import, these three character sets are required to be imported in a correct way. 
 
2.1 Querying the character set of the Oracle server side
There are many ways to identify the Oracle server-side character set, and the more intuitive query method is the following:
sql> Select Userenv (' language ') from dual;
USERENV (' LANGUAGE ')
----------------------------------------------------
simplified Chinese_china. ZHS16GBK
 
sql>select userenv (' language ') from dual;
AMERICAN _ AMERICA. ZHS16GBK
 
2.2 How to query the character set of a DMP file
The dmp file exported with the Oracle Exp tool also contains character set information, and the 2nd and 3rd bytes of the DMP file record the character set of the DMP file. If the DMP file is small, such as only a few m or dozens of m, you can open it with UltraEdit (16 binary), look at the 2nd 3rd byte of content, such as 0354, and then use the following SQL to isolate its corresponding character set:
sql> Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual;
ZHS16GBK
 
If the DMP file is large, such as more than 2G (which is also the most common case), with a text editor opened very slowly or completely open, you can use the following command (on the UNIX host):
cat exp.dmp |od-x|head-1|awk ' {print $ |cut-c} ' 3-6
the corresponding character set can then be obtained using the SQL above. 
 
2.3 Querying the character set of the Oracle client side
under the Windows platform, it is the Nls_lang of the corresponding oraclehome in the registry. You can also set it in the DOS window itself,
For example: Set Nls_lang=american_america. ZHS16GBK
this will only affect the environment variables in this window. 
 
under the UNIX platform, it is the environment variable Nls_lang. 
$echo $NLS _lang
American_america. ZHS16GBK
 
If the result of the check finds that the server side is inconsistent with the client-side character set, uniformly modify the same character set as the server side. 
 
Add:
(1). Database server Character Set
SELECT * from Nls_database_parameters
from Props$, is the character set that represents the database. 
 
(2). Client Character Set Environment
SELECT * from Nls_instance_parameters
It originates from V$parameter, which represents the setting of the client's character set, which may be a parameter file, an environment variable, or a registry
 
(3). Session Character Set Environment
SELECT * from Nls_session_parameters
from V$nls_parameters, which indicates the session's own settings, may be the session environment variable or alter session completion, if the session has no special settings, will be consistent with nls_instance_parameters. 
 
(4). The client's character set requires consistency with the server to correctly display non-ASCII characters for the database. 
If multiple settings exist, NLS priority level: SQL function > Alter SESSION > Environment variable or Registry > parameter file > database default Parameters
 
Character set requirements are consistent, but language settings can be different, language settings are recommended in English. If the character set is ZHS16GBK, then Nls_lang can be AMERICAN_AMERICA.ZHS16GBK.

The main reason for Chinese garbled is that the character set is different. In Oracle, we care about the character sets in three places:

First look at the database character set:
Select Userenv (' language ') from dual may be used in Oracle or: select Name, value$ from props$;

L Character Set inside Oracle server
Modification Method:
Connect Sys/chang_on_install
Update props$ set value$= ' traditional Chinese_taiwan. Al32utf8 ' where name= ' nls_characterset ';
Commit

2 The character set stored in the Nls_lang variable

This is a variable that is set by Oracle. In Windows, this variable is saved in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 holds the Nls_lang variable.

In Unix/linux, you need to set it yourself and add Nls_lang=american_america to the. Profile. Zhs16gbk
Export Nls_lang

3 Character set for client applications

Here is the character set I used
American_america. Al32utf8
Traditional Chinese_taiwan. zht16mswin950
Traditional Chinese_taiwan. Al32utf8
American_america. We8iso8859p1
American_america. Zhs16gbk

Special NOTE: If the server is encoded as AL32UTF8, then the client should install its own environment to set the character encoding.
For example, I have a server side, where two clients one for the simplified, one for the traditional:
Server-side Character set: American_america. Al32utf8
Simplified client Character set: American_america. Zhs16gbk
Traditional client Character Set settings: traditional Chinese_taiwan. zht16mswin950

View and set the Oracle database character set