- 论坛徽章:
- 0
|
之前安装LINUX +ORACLE 的时候都是选择的英文字，字符集的问题，没太在意过
昨天重新安装的时候，把linux安装成中文，
然后再安装oracle的时候把字符集也改成了zhs16gbk 而国家字符集依旧用的al16utf16，
然后不期而是致的乱码，问号等等，搞得头大，今天终于解决掉了，在这里和大家分享一下我实际测试的效果！！
环境
服务器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 没特别修改过，估计简体中文^_^
数据库字符集及数据库国家字符集查询结果
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添加两条环境变量
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参数其字符集必须与操作系统本身一致,否则,即使是当前环境数据是正常的.换一个正常环境后,必定会乱码
以下我自己的测试
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
果然发现这个很隐蔽的错误，而且是几乎不可逆的，因为把错误的代码写到了数据库里面。
当然如果希望让SQLPLUS 显示中文也可以把NLS参数设成如下
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
最后的总结：
在客户端操作系统字符集为zh_CN.utf8的情况下
NLS_LANG参数的几部分
export NLS_LANG=american_america.utf8
国家 区域 字符集
想显示英文用：
AMERICAN_AMERICA.UTF8
想显示中文用：
SIMPLIFIED CHINESE_CHINA.UTF8
字符集必须是使用UTF8与客户端操作系统保持一致 |
|
免费注册
发表于 2013-11-22 17:59
发表于 2013-11-22 20:50
