- 論壇徽章:
- 0
|
之前安裝LINUX +ORACLE 的時候都是選擇的英文字，字符集的問題，沒太在意過
昨天重新安裝的時候，把linux安裝成中文，
然后再安裝oracle的時候把字符集也改成了zhs16gbk 而國家字符集依舊用的al16utf16，
然后不期而是致的亂碼，問號等等，搞得頭大，今天終于解決掉了，在這里和大家分享一下我實際測試的效果！！
環(huán)境
服務器OS:Linux Redhat Enterprise 5 64位
服務器OS字符集
[oracle@oraclelzw ~]$ locale
LANG=zh_CN.UTF-8
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=
客戶端OS:WIN7 64位 客戶端工具 ORACLE SQL DEVELOPER TOOLS
字符集:936 沒特別修改過，估計簡體中文^_^
數(shù)據(jù)庫字符集及數(shù)據(jù)庫國家字符集查詢結果
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
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_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0
???????
就是這問號看著不爽~~~
[oracle@oraclelzw ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 22 17:02:01 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
???:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
在.bash_profile添加兩條環(huán)境變量
export NLS_LANG=american_america.UTF8
export LANG=en_US.UTF8
修改后登錄
[oracle@oraclelzw ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 22 17:00:48 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select * from nls_database_parameters;
PARAMETER VALUE
-------------------- ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTE .,
RS
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXFF AM TZR
MAT
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERS AL16UTF16
ET
NLS_RDBMS_VERSION 11.2.0.1.0
20 rows selected.
SQL> select * from nls_session_parameters;
PARAMETER VALUE
-------------------- ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTE .,
RS
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXFF AM TZR
MAT
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
問題好像是解決了，但還是擔心，之前老師講過，不可以隨便修改字符集的設置
設定字符集的原則 NLS_LANG參數(shù)其字符集必須與操作系統(tǒng)本身一致,否則,即使是當前環(huán)境數(shù)據(jù)是正常的.換一個正常環(huán)境后,必定會亂碼
以下我自己的測試
LINUX OS字符集是 UTF8
當我把NLS_LANG＝american_america.zhs16gbk
SQL> select dump('中國',1016) from dual;
DUMP('中國',1016)
-----------------------------------------------------
Typ=96 Len=6 CharacterSet=ZHS16GBK: e4,b8,ad,e5,9b,bd
在WINDOWS 下的ORACLE SQL DEVELOPER TOOLS （因為沒做特定的修改此處肯定是一致的）
Typ=96 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa
果然發(fā)現(xiàn)這個很隱蔽的錯誤，而且是幾乎不可逆的，因為把錯誤的代碼寫到了數(shù)據(jù)庫里面。
當然如果希望讓SQLPLUS 顯示中文也可以把NLS參數(shù)設成如下
export NLS_LANG=SIMPLIFIED\ CHINESE_CHINA.UTF8
這樣，在SQLPLUS登錄以后，部分顯示為中文，像我這種英文不太好的同志，學習起來會方便一些
[oracle@oraclelzw ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 22 17:30:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
連接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
當前會話被修改了：
SQL> select * from nls_session_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
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_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已選擇17行。
SQL> select dump('中國',1016) from dual;
DUMP('中國',1016)
--------------------------------------------------------------------------------
Typ=96 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa
和之前在 ORACLE SQL DEVELOPER TOOLS DUMP的結果一致~~OK
最后的總結：
在客戶端操作系統(tǒng)字符集為zh_CN.utf8的情況下
NLS_LANG參數(shù)的幾部分
export NLS_LANG=american_america.utf8
國家 區(qū)域 字符集
想顯示英文用：
AMERICAN_AMERICA.UTF8
想顯示中文用：
SIMPLIFIED CHINESE_CHINA.UTF8
字符集必須是使用UTF8與客戶端操作系統(tǒng)保持一致 |
|
免費注冊
發(fā)表于 2013-11-22 17:59
發(fā)表于 2013-11-22 20:50
