數據庫中文亂碼ORACLENLS_LANGLINUXLANG
要維護數據庫時候 經常使用LINUX系統和遠程SHELL客戶端連接到ORACLE數據庫. 使用上面的SQLPLUS LSNRCTL RMAN工具
經常檢查TRC文件,ORA文件 等. 要是出現亂碼就糟糕了.
因為 你來之前別人已經裝好了ORACLE 並且設置了中文.....
以前習慣了英文... 所以被中文撞了腰.
終端使用的 PUTTY SSH SECUTP XMAGRE等
這裡涉及到系統 和數據庫 兩塊東. 不要搞亂自己哦
EXPORT LANG ==> 是定義系統語言的
EXPORT NLS_LANG==> 定義數據庫語言的
這些 我一般放在/home/oracle/.bash_profile 其他人喜歡放在/home/oracle/.bashrc裡
設置之前了解下系統目前語言和支持的語言.
先講 系統的LANG
cat /etc/sysconfig/i18n 系統默認語言
/usr/share/i18n/SUPPORTED 包含系統支持的本地語言
/usr/local/share/i18n/SUPPORTED 添加自定義的本地語言到這個文件
我的LINUX系統默認是英文 附加了中文支持
因此我的ORACLE用戶下的環境變量 LANG=zh_CN.UTF-8
這樣 保存 然後source /home/oracle/.bash_profile 生效下
df -h 中文標題出來了.
語言(Language), 地域 (Territory) 和字符集(Codeset)。一個locale的書寫格式為: 語言[_地域[.字符集]].
注意是 小寫 下劃線 大寫 點 大寫
其他寫法默認無效,不提示錯誤,讓你轉圈圈去
然後接著講下數據庫的NLS_LANG
哈哈 就是前面多了個NLS_
我的數據庫默認裝的英文 外掛下中文.
所以了先看下數據庫的設置
select * from v$NLS_parameters
看主要的NLS_LANGUAGE ; NLS_TERRITORY;NLS_CHARACTERSET
我的上面分別是 AMERICAN;AMERICA;AL32UTF8
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
注意狗屎的細節 我們中國沒有統一,所以有簡體和繁體 .瞧上面 有空格是不? 就是因為這個台灣海峽在,就得上雙引號.其他的語言類別就不用""...
另外注意點狗屎問題.ORACLE數據庫的字符集 UTF8 不是UTF-8 注意干字.
系統變量有干 數據庫則沒有.
來點官方資料 充點門面. 狗屎的華為一天打三次電話 叫我去面試. 兩個月累積下來有20次電話. 爺是專科生好不好啊 別這樣玩我.知道你華為要E英文SB級.
Setting the NLS_LANG Environment Variable for Oracle Databases
Follow this procedure to set the NLS_LANG environment variable for Oracle databases.
To set the NLS_LANG environment variable for Oracle databases
Determine the NLS_LANG value.
In the data warehouse database, run the command
SELECT * FROM V$NLS_PARAMETERS
Make a note of the NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8
For Windows:
Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
In System variables section, click New.
In the Variable Name field, enter NLS_LANG.
In the Variable Value field, enter the NLS_LANG value that was returned in Step 1.
The format for the NLS_LANG value should be [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8.
For UNIX, set the variable as shown below:
setenv NLS_LANG <NLS_LANG>
For example: setenv NLS_LANG American_America.UTF8.
If your data is 7-bit or 8-bit ASCII and the Informatica Server is running on UNIX, then set
NLS_LANG <NLS_LANGUAGE>_<NLS_TERRITORY>.WE8ISO8859P1
CAUTION: Make sure you set the NLS_LANG variable correctly, as stated in this procedure, or your data will not display correctly.
Reboot the machine after creating the variable.
The NLS_LANG parameter is stored in the registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG subkey, where ID is the unique number identifying the Oracle home.
The NLS_LANG parameter uses the following format:
NLS_LANG = LANGUAGE_TERRITORY.CHARACTER_SET
where:
Parameter Description
LANGUAGE Specifies the language and conventions for displaying messages, day name, and month name.
TERRITORY Specifies the territory and conventions for calculating week and day numbers.
CHARACTER_SET Controls the character set used for displaying messages.
C.2 Commonly Used Values for NLS_LANG
Table C-1 lists commonly used NLS_LANG values for various operating system locales:
Table C-1 NLS_LANG Parameter Values
Operating System Locale NLS_LANG Value
Chinese (PRC) SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Chinese (Taiwan) TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
English (United Kingdom) ENGLISH_UNITED KINGDOM.WE8MSWIN1252
English (United States) AMERICAN_AMERICA.WE8MSWIN1252
French (Canada) CANADIAN FRENCH_CANADA.WE8MSWIN1252
French (France) FRENCH_FRANCE.WE8MSWIN1252
German (Germany) GERMAN_GERMANY.WE8MSWIN1252
Japanese JAPANESE_JAPAN.JA16SJIS
Korean KOREAN_KOREA.KO16MSWIN949
Russian RUSSIAN_CIS.CL8MSWIN1251