A simple diagram of the Oracle character set, Chinese garbled solution ____oracle

Often encountered sqlplus display garbled problems, character sets and related definitions are explained but very few can put these relations are very simple to understand.

Before that we need to figure out three concepts, operating system character set, client character set, Oracle Character set:

Operating system Character set: the corresponding parameter is lang, this parameter should be a superset of the Oracle database, if the operating system does not support, then our data will be garbled. The operating system here refers to the client's operating system. The server-side operating system does not affect data access.

Database Character Set: Nls_characterset, you can view the character set of the current database in the Nls_database_parameters, choose when you install the database, generally do not modify, but the new character set is the strict superset of the existing character set can be changed, In other cases, modifications can result in database exceptions. For example, modify the UTF8 character set to Al32utf8

For the mapping of subset superset, see the following Oracle official website document Binary Subset-superset pairs.

http://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG591

Client Character set: the corresponding parameter is Nls_lang, and if the client is not set, then the default parameter for the database at installation time is taken

In order to help understand, I drew a picture as follows, figure the red part of the winning is consistent to represent the data stored in the same way, that is, if LANG, Nls_lang, nls_characterset encoding is consistent, such as UTF8, then the data transmission process will not be abnormal, character garbled just display problems.

1. Operating system Character Set

Linux first locale view character set

[oracle@oddpc ~]$ locale
Lang=en_us. UTF-8
lc_ctype= "en_US. UTF-8 "
lc_numeric=" en_US. UTF-8 "
lc_time=" en_US. UTF-8 "
lc_collate=" en_US. UTF-8 "
lc_monetary=" en_US. UTF-8 "
lc_messages=" en_US. UTF-8 "
lc_paper=" en_US. UTF-8 "
lc_name=" en_US. UTF-8 "
lc_address=" en_US. UTF-8 "
lc_telephone=" en_US. UTF-8 "
lc_measurement=" en_US. UTF-8 "
lc_identification=" en_US. UTF-8 "
lc_all=
[oracle@oddpc ~]$ echo $LANG
en_US. UTF-8

2, the host does not install Chinese support package, set Lang can be the effect as follows, obviously no way to adjust Nls_lang on this machine can not show Chinese

[ORACLE@EVENPC ~]$ export Lang=zh_cn.utf8
[ORACLE@EVENPC ~]$ date
2016???? 15:17:01 CST

3, the installation of Chinese support package, the use of yum-y Groupinstall Chinese-support can be installed in Chinese support package, installation process skipped, after installation can be normal display of Chinese

[ORACLE@ODDPC ~]$ export Lang=zh_cn.utf8
[oracle@oddpc ~]$ date
October 13, 2016 Thursday 15:14:19 CST

4, the next is to show the test, I installed two database instances PROD1 and Prod5,prod1 's character set is We8mswin1252,prod5 's character set is Al32utf8

Nls_lang is empty by default, at which point Nls_lang the value of the default installation, PROD1 Amrican,prod5 is Simplified Chinese

[Oracle@oddpc ~]$ Echo $NLS _lang

Sql> show parameter lang        

NAME                                 TYPE        VALUE
----------------------------------------------------- ------------------------
nls_date_language                    string
nls_language                         string      American

Sql> select Sysdate from dual;

Sysdate
---------
13-oct-16

PROD5

Sql> show parameter lang  

NAME                                 TYPE        VALUE
----------------------------------------------------- ------------------------
nls_date_language                    string
nls_language                         string      Simplified Chinese

Sql> select Sysdate from dual;

Sysdate
----------
13-10?-16

5, PROD5 occurred garbled, PROD1 English Normal, set the Nls_lang parameters

PROD1 results are as follows, you can see that the message has become Chinese, but because the character set is not UTF8 characters stored in the garbled

[ORACLE@ODDPC ~]$ export nls_lang= "simplified Chinese_china. UTF8 "
[oracle@oddpc ~]$ sqlplus/as sysdba

sql*plus:release 11.2.0.3.0 Production on Thu October 13 15:42:46 2016< C14/>copyright (c) 1982, Oracle.  All rights reserved.


Connecting to: 
Oracle Database 11g Enterprise Edition release 11.2.0.3.0-production
with the partitioning, OLAP, Data Mi Ning and real application testing options

Sql> show parameter lang NAME TYPE VALUE----------------                    -----------------------------------------------------------------------------------Nls_date_language                            String Simplified Chinese nls_language string                              Simplified Chinese sql> show parameter db_name name TYPE VALUE---------------------------------------------------------------------------------------------------db_                                 Name string PROD1 sql> show parameter lang NAME TYPE VALUE--------------------------------------------------------------- ------------------------------------nls_date_language String Simplified CH              Inese Nls_language           String Simplified Chinese sql> select sysdate from dual; Sysdate------------13-10? -16

Sql> select * from Nls_database_parameters; PARAMETER VALUE--------------------------------------------------------------------------                             ------nls_language American Nls_territory AMERICA nls_currency $ nls_iso_currency AMERICA nls_numeric_characters., NL                          S_characterset we8mswin1252 Nls_calendar Gregorian Nls_date_format                                 DD-MON-RR nls_date_language American Nls_sort BINARY Nls_time_format HH.MI. Ssxff AM PARAMETER VALUE---------------------------------------------------------------- ----------------Nls_timestamp_format dd-mon-rr HH.MI. Ssxff AM Nls_time_tz_format HH.MI. Ssxff AM TZR Nls_timestamp_tz_formaT DD-MON-RR HH.MI. Ssxff AM tzr nls_dual_currency $ nls_comp BINARY nls_length_semanti CS BYTE nls_nchar_conv_excp FALSE nls_nchar_characterset al16ut F16 nls_rdbms_version 11.2.0.3.0 has selected 20 rows.

The result of PROD5 is as follows, at this time PROD5 display normal

[ORACLE@ODDPC ~]$ export nls_lang= "simplified Chinese_china.  UTF8 "[oracle@oddpc ~]$ sqlplus/as sysdba sql*plus:release 11.2.0.3.0 Production on Thursday October 15:46:36 2016 Copyright  (c) 1982, Oracle.


All rights reserved. Connecting to: Oracle Database 11g Enterprise Edition release 11.2.0.3.0-production with the partitioning, OLAP, Data Mining and                              Real application testing Options sql> show parameter db_name name TYPE
VALUE---------------------------------------------------------------------------------------------------

Db_name string PROD5 sql> select Sysdate from dual;                              Sysdate------------1 March-October -16 sql> show parameter lang NAME TYPE VALUE---------------------------------------------------------------------------------------------------Nls_        Date_language string                    Simplified Chinese nls_language string Simplified C Hinese

Sql> select * from Nls_database_parameters; PARAMETER VALUE--------------------------------------------------------------------------                             ------nls_language American Nls_territory AMERICA nls_currency $ nls_iso_currency AMERICA nls_numeric_characters., NL                          S_characterset Al32utf8 Nls_calendar Gregorian Nls_date_format DD-MON-RR nls_date_language American Nls_sort BIN ARY Nls_time_format HH.MI. Ssxff AM PARAMETER VALUE---------------------------------------------------------------- ----------------Nls_timestamp_format dd-mon-rr HH.MI. Ssxff AM Nls_time_tz_format HH.MI.   Ssxff AM TZR Nls_timestamp_tz_format               DD-MON-RR HH.MI. Ssxff AM tzr nls_dual_currency $ nls_comp BINARY nls_length_semanti CS BYTE nls_nchar_conv_excp FALSE nls_nchar_characterset al16ut F16 nls_rdbms_version 11.2.0.3.0 has selected 20 rows.

Summary: Through the above experiments can be seen, the client shows whether the garbled is determined by Nls_lang, the occurrence of Chinese garbled, the first to see whether the database Nls_characterset support Chinese storage, if not supported, regardless of the settings are not normal display of Chinese. The code support for various languages is given in the Oracle official documentation.

http://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG593

Table A-13 Languages and Character Sets supported by Lcsscan and GDK

Language	Character Sets
Arabic	Al16utf16, Al32utf8, AR8ISO8859P6, ar8mswin1256, UTF8
Bulgarian	Al16utf16, Al32utf8, CL8ISO8859P5, cl8mswin1251, UTF8
Catalan	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Croatian	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Czech	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Danish	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Dutch	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
中文版	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Estonian	Al16utf16, Al32utf8, NEE8IOS8859P4, UTF8
Finnish	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
French	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
German	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Greek	Al16utf16, Al32utf8, EL8ISO8859P7, el8mswin1253, UTF8
Hebrew	Al16utf16, Al32utf8, IW8ISO8859P8, iw8mswin1255, UTF8
Hindi	Al16utf16, Al32utf8, In8iscii, UTF8
Hungarian	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Indonesian	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Italian	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Japanese	Al16utf16, Al32utf8, ISO2022-JP, Ja16euc, Ja16sjis, UTF8
Korean	Al16utf16, Al32utf8, Iso2022-kr, ko16ksc5601, ko16mswin949, UTF8
Latvian	Al16utf16, Al32utf8, NEE8ISO8859P4, UTF8
Lithuanian	Al16utf16, Al32utf8, NEE8ISO8859P4, UTF8
Malay	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Norwegian	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Persian	Al16utf16, Al32utf8, ar8mswin1256, UTF8
Polish	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Portuguese	Al16utf16, Al32utf8, Us7ascii, UTF8, We8iso8859p1, WE8ISO8859P15, we8mswin1252
Romanian	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Russian	Al16utf16, Al32utf8, CL8ISO8859P5, cl8koi8r, cl8mswin1251, ru8pc866, UTF8
Serbian	Al16utf16, Al32utf8, CL8ISO8859P5, cl8mswin1251, UTF8
Simplified Chinese	Al16utf16, Al32utf8, hz-gb-2312, UTF8, ZHS16GBK, zhs16cgb231280
Slovak	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Slovenian	Al16utf16, Al32utf8, EE8ISO8859P2, ee8mswin1250, UTF8
Spanish