oracle学习笔记 字符集正确设置及相关操作

oracle学习笔记 字符集正确设置及相关操作

这节课讲的是字符集针对DBA来讲该如何去关注的问题

以后在weblogic在备份恢复里面还要继续关注字符集的概念

一）oracle环境

DBA来讲我们常见的环境是

用windows做客户端

服务器端
操作系统OS是linux或unix
和oracle实例

客户端是sqlplus还有客户端操作系统

对DBA来讲这种环境
往往通过sqlplus连接到linux或者是unix操作系统上面运行的oracle上

这是我们常见的环境通过网络连上去

客户端操作系统往往是windows，比如win7
服务器端操作系统是linux或者unix

现在一些新的环境越来越兴起，而且用的越来越多
因为它们的性能不错而且价格比较低廉
再说感觉上可靠性要好一些
当然了要论性能跟可靠性
无论是性能还是可靠性方面还是unix更好一些

但是unix服务器
包括相关的服务以及硬件相当昂贵
所以说现在大家linux用的越来越多

但是对大的行业
像金融行业等等这些
它还是偏向于unix
比如国内的央企以及一些跨国性的企业
还是倾向于unix

二）oracle用到字符集的地方

这里面有几个地方需要字符集

第一对windows这个操作系统本身有字符集
也就是客户端win7有字符集
我们用chcp命令可以看
一般用中文字符集

然后oracle数据库软件有字符集
是第二个位置

客户端有字符集，oracle软件也有字符集
而且还有很多字符集

oracle软件的操作系统本身也有字符集
服务端操作系统是第三个位置

客户端sqlplus软件没有字符集
sqlplus里面可以显示中文，也输入中文
它本身没有字符集，它调用操作系统字符集

有一个原则
如果软件有字符集
这时操作系统字符集就失效，就不用了

如果sqlplus有字符集
意味着
我在sqlplus里输入中文以后
sqlplus就会调用自己的字符集
把中文直接转成编码然后存到操作系统里面去
这时我们就不用操作系统字符集了

既然sqlplus没有字符集
它就使用操作系统字符集

对oracle来讲，oracle数据库本身有字符集

所以说对oracle数据库所在的操作系统服务器的字符集它不用
我们在研究服务端这个体系结构里面字符集的时候
我们忽略了oracle数据库所在的服务器的操作系统字符集
服务端服务器不管是什么字符集和oracle没关系

即使你的操作系统是英文版
比如我们装unix的时候往往装英文版
但是oracle里面照样可以存中文
因为字符集方面它和操作系统没有任何关系

但是sqlplus没有中文没有字符集
这时候sqlplus要显示中英文的时候
必须使用操作系统

所以说研究字符集要研究两个地方
第一个地方是客户端操作系统的
第二是oracle数据库的

记住刚才讲的一个概念
如果这个软件本身有字符集
操作系统字符集对它来讲就没有任何意义
如果sqlplus有字符集
操作系统字符集我们就不用它，我们不考虑它
就因为sqlplus没有字符集
所以说操作系统字符集很重要
它会用到操作系统字符集

三）发生在oracle的字符集转换

对oracle当前这个结构来讲
所有的字符集的转换都是在oracle端发生的

因为oracle支持多种字符集
字符集和字符集转换的时候只有oracle能做到
都是在oracle端执行的
当然说的是前面讲的结构里

除了客户端os的字符集
oracle软件的字符集
还有一个地方需要关注

在sqlplus里面输入中文
比如输入中文“中国”
sqlplus马上调用操作系统把中文转成编码
“中国”这两个字符被操作系统进行编码
编码出几个数字
使用到中文操作系统中文的编码表

然后这个编码
从客户端传到了oracle
oracle拿到这个编码以后又要转成字符
这个时候oracle必须知道它接受到的编码使用什么编的

也就是说
客户端使用sqlplus，客户端有操作系统
服务端有数据库oracle和服务端操作系统

我在sqlplus输入一条命令
insert into t2 values(1,’甲骨论’);

这里面有字符了
因为操作系统是中文操作系统
这个字符经过操作系统编码以后
编成了甲骨论
比如编成了 A4 13 25 46 78 DD
这是16进制数

“甲骨论”这三个汉字
经过中文操作系统的中文字符集的编码以后
编成了A4 13 25 46 78 DD这个编码
然后传到了oracle

oracle接受到这个sql语句以后
打开这个sql语句一看
sql语句是
insert into t2 values(1, 然后后面是A4 13 25 46 78 DD这个编码

oracle要把A4 13 25 46 78 DD存到t2去

存到t2的时候
oracle发现
要存的t2这张表其中一个列name那个列是varchar列
oracle马上就反应过来了
oracle要往varchar列里面存字符的时候它要用编码
而从客户端传过来的就是已经编好码的了

这时对oracle来讲
第一个选择
oracle直接存进去
第二个选择
oracle问一下你是用什么编的码
它需要问一下，这样有必要

oracle问一下用什么编的码
客户端告诉它用中文编的码

oracle一看我用的数据库也是中文，中文字符集
你用中文编的码
显然我不需要进行转换了直接就存进去

假设数据库不是中文字符集是用的unicode
比如说用的是Al32utf8
这个时候对oracle来讲也可以存中文

但是它经过询问以后
它发现客户端用中文编的码

这时候对oracle来讲只能接受Al32utf8的编码
但客户端是用的16gbk编的码

这个时候oracle
首先将这个A4 13 25 46 78 DD字符编码转成中文
因为oracle有中文字符集
将编码转成中文转成字符

拿着字符再去Al32utf8字符集里面
把这个字符再转成unicode也就是Al32utf8的编码
然后存到数据库里面去

也就是当oracle要存储字符的时候
它一定会问客户端给的编码是用什么编的

如果这个编码
比如说客户端的字符集和oracle的字符集一样的话
这时oracle就直接存进去

如果不一样的话
oracle需要首先将这个编码转成字符
然后再使用数据库的字符集将这个字符转成编码再存进去

四）oracle编码转换的中间结果

这里老师把在oracle数据库的转码过程的中间结果说成是字符
没有说这个“字符”到底是什么

这里说的不应该是字模

字模是一种点阵的形式
一般ascii字符字模是8*16的形式
对汉字12*12字模其占用的字节数是12*12/8=16字节
汉字点阵字模还有16*16点、24*24点、32*32点，48*48点几种
每个汉字字模分别需要32、72、128、288个字节存放

字模的体积很大用它来做字符的比较不现实

更何况现在使用的很多矢量字符，更无法比较

所以这个作为中间结果的字符应该也是一种编码

windows中转码时
是使用unicode编码作为中间结果
因为它基本包括了世界上所有的使用字符

而对于oracle软件
它的一个主要任务就是保存和处理各种字符
世界上的每种文字的编码在oralce软件内部都有自己的编码表

所以这个中间字符对oracle应该是它自己的一个字符集
因为它也有制作编码表的能力并且必须有这个能力
虽然oracle内也包含转码常用的unicode编码表但未必使用的是这个

既然有自己的字符集，至于具体使用的字符集是什么不再重要

应该有很多的转码表可以在使用字符和这个字符集之间进行相互的转码
已达到转换字符集编码的能力

五）转码过程

oracle端发生了什么

oracle有两个字符集
一是中文，一是unicode

一个是32 Al32utf8
一个是16 16GBK

这时候oracle接受到一串字符
一串字符编码
一串编码
这个编码是用中文编的

但是oracle是unicode字符集

oracle中16GBK字符集的编码表
一个编码对应一个字符
先在这里拿着编码找到字符
在Al32utf8编码表
中再拿着字符再找到新的编码
然后把编码存到数据库里面去

对oracle来讲
如果它发现客户端的字符集和自己的字符集不一样
会将传过来的所有的字符编码在一个编码转换表先转成字符
字符再在另一个字符编码转换对应表中再转成编码
再存进去

也就是说orale总是以自己的数据库字符集作为标准来存储的

在oracle数据库里面比如是Al32utf8字符集
oracle就要求所存储的所有的字符
都是使用这个Al32utf8字符集去编码的

这是oracle的一个原则

六）字符乱码

这个时候有可能出现一种情况

比如客户端是Al32utf8，数据库是16GBK

数据库中的两个转换表
Al32utf8转字符 和 字符转16GBK 这两个表

有可能
客户端传了一堆编码过来
oracle发现它是用unicode做的编码
但是oracle是16GBK

显然客户端和oracle不一样
oracle就要转码
转码是在oracle端发生的

首先拿着这个编码
先在一个编码转换表中转成字符
再拿着字符在另一个编码转换表中转成编码

因为unicode字符集它的字符以及16GBK中都有中文

unicode转成字符没问题
结果在第二个转换表中字符转GBK时字符找不到编码
有可能找不到编码
这时候oracle就会存放个问号
存一个问号进去，就会出现字符的丢失了

这就是一种叫字符乱码的一种出现情况

七）oracle是如何存字符的

oracle只有在往varchar char clob long这些列中存字符的时候才会用到字符集

当然取字符时也会用到字符集
我们现在讲存

oracle接收到的一定是编码

oracle需要问客户端你给我的这个编码是用什么编的

如果说oracle发现客户端的编码
和自己的数据库的字符集是一样的
它就直接存进去
如果不一样它就要字符集转换
因为oracle有非常多的字符集
你来的任何编码
我都可以转成相应的字符
然后字符再找到编码再重新编放进去

oracle保证所存储的所有的字符
都是用oracle数据库的字符集去编码的

八）如何知道客户端的字符集

oracle如何知道客户端的字符集这是个问题

有学生会认为可以登陆可以访问操作系统
oracle这个软件它访问不了客户端操作系统

对oracle数据库来讲有三个地方涉及到字符集

客户端操作系统本身有字符集这个毫无疑问
另外一个是oracle数据库有字符集

操作系统字符集对windows来讲
用chcp看

对linux用locale
可以看到操作系统字符集是什么

oracle数据库的字符集
我们查nls_database_parameters
上节课查了有两个字符集

数据库字符集知道了
客户端操作系统字符集知道了
对服务器端操作系统字符集我们不理它
还有一个地方涉及到一个字符集

客户端操作系统字符集它是用来编码的

oracle数据库的字符集也是用来编码的
它除了编码以外还要转码
对客户端它只编码

还有一个字符集，是一个参数NLS_LANG
National Language Support (NLS)
NLS_LANG=_.

这个参数在客户端设

在windows的cmd命令提示符窗口设
set NLS_LANG=语言+地区+字符集
首先是语言american
再就是地区america
后面是字符集

C:\Documents and Settings\Administrator>set NLS_LANG=american_america.zhs16gbk

C:\Documents and Settings\Administrator>echo %NLS_LANG%
american_america.zhs16gbk

再打开一个命令提示符窗口

C:\Documents and Settings\Administrator>echo %NLS_LANG%
%NLS_LANG%

没有值说明set后这个参数只在一个命令窗口有用

如果在windows系统环境变量设置了NLS_LAGN
所有打开的命令提示符下会默认使用这个环境变量设置

没有这个环境变量时
C:\Documents and Settings\Administrator>set NLS_LANG
环境变量 NLS_LANG 没有定义

设置了这个环境变量后

C:\Documents and Settings\Administrator>set NLS_LANG
NLS_LANG=american_america.zhs16gbk

实验使用的软件PUTTY是linux的终端
其中的命令行使用的是linux中的环境
即使在所在windows下设置了环境变量NLS_LANG
对登陆到linux的putty中的终端是没有影响的

它默认显示出来的NLS_LANG的值
其实是登陆用户oracle用户使用的NLS_LANG参数

[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.zhs16gbk

执行这个命令时我的环境，这时
linux服务器的字符集是utf8
windows代码页是936
windows环境变量NLS_LANG的值是SIMPLIFIED CHINESE_CHINA.ZHS16GBK
PUTTY软件的字符集是utf8

虽然oracle数据库使用的american america zhs16gbk
但这时还没有使用数据库

putty命令行执行echo $NLS_LANG时是在oracle用户下执行的
所以它返回了oracle用户的环境变量NLS_LANG的值
前面的课程中为这个用户指定了NLS_LANG的值就是american_america.zhs16gbk

在终端先切换到root用户

[oracle@redhat4 ~]$ su -
Password:
[root@redhat4 ~]# echo $NLS_LANG

没有这个参数

再切换到oracle用户

[root@redhat4 ~]# su - oracle
[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.zhs16gbk

有这个参数，说明是oracle用户的参数

当前关于语言和地区先不管它
后面是zhs16gbk字符集

把NLS_LANG设成zhs16gbk
第一是客户端设的
第二它的意义
oracle想要知道客户端操作系统的字符集是什么
可以通过这个参数

这个参数设成16GBK了
oracle就认为是16GBK
客户端操作系统到底是什么字符集
oracle不知道
oracle就通过这个参数去知道

其实这个参数就是告诉oracle
我的客户端的字符集是多少
NLS_LANG它是传话的

但NLS_LANG传话就有可能存在把话传错了

九）如何设置字符集

oracle有三个字符集
这里重点强调一下这三个字符集该如何设置

1）客户端

对客户端操作系统
我买的什么操作系统它就是什么字符集
这个没有办法
对中国人来讲
我操作系统字符集就是两个
第一个是utf8
第二就是中文
可以设unicode，也可以设中文

这是oracle客户端
对linux来讲经常用utf8
对windows来讲经常用中文
utf8和中文字符集都可以显示中文
因为我们是中国人都用这两个

2）oracle

对oracle来讲
oracle数据库在建库的时候可以选择字符集
我们的原则
将来oracle要存什么就用什么字符集
要存中文就用中文字符集
要存中日韩多国语言那就用unicode
如果不考虑性能问题
存中文也可以用unicode

如果只存英文用us7
但是对中国人来讲
主要用中文、简体中文或者是unicode

3）NLS_LANG

NLS_LANG这个参数的设置非常非常重要

要和客户端操作系统字符集一样

比如我们客户端操作系统字符集是中文
那么我这个NLS_LANG就设成zhs16gbk
如果客户端操作系统是utf8就设utf8

一定要记住
NLS_LANG的设置就随客户端操作系统字符集一样

即不要和服务端oracle一样
也不要随意去造
跟客户端一样就行了
如果你掌握这个原则了
oracle的字符集永远不会出问题

locale、locale -a、chcp
这三个命令是分别看linux和windows字符集的型号

如何正确设置字符集也讲了

十）NLS_LANG设置出现问题的情况

字符集出现问题有两种情况：
存储的是错误的字符编码
存储的是正确的字符编码

最经常出现的情况

1）NLS_LANG设为了其它的字符集

如：
客户端字符集是WE8ISO8859P15一种西欧字符集
ORACLE的字符集是AL32UTF8

对客户端和oracle来讲没有问题
因为oracle字符集AL32UTF8可以存储WE8ISO8859P15字符集

但是NLS_LANG设的是WE8MSWIN1252
NLS_LANG这个字符集应该等于WE8ISO8859P15但是没有

这就有问题
不用管别的，这是个错误的设置

2）NLS_LANG设成了和数据库一样

还有种错误的设置

NLS_LANG设成UFT8
但实际上客户端操作系统字符集是WE8ISO8859P15
数据库字符集是UTF8
把NLS_LANG设成和服务器段数据库字符集一样了
这是一个最隐蔽的一个错误
平时在用的时候你发现没有问题
但是这是有问题的

客户端操作系统WE8ISO8859P15字符集是欧洲的一个语言

平时存的时候
要存一个字符欧元符号€
这是个特殊的字符

客户端的操作系统要存这个字符
客户段操作系统就根据字符编码
把字符转成A4
在WE8ISO8859P15字符集欧元的字符编码是A4
操作系统自己转的没问题

然后传到服务器端
A4传到数据库了

oracle一看是字符
就问客户端用啥编的
客户端就用NLS_LANG告诉ORACLE是UTF8编的

oracle一看自己是utf8
NLS_LANG告诉它也是UTF8
oracle就直接把A4存到数据库里面去没有转换

但实际上存储的这个字符是不对的
因为我们知道数据库中的这个A4字符
实际上是使用的WE8ISO8859P15字符集编码的
而oracle字符集是UTF8

也就是违背了一个原则
oracle数据库里面存储的所有的字符都应该用utf8去编码
但实际上它存储了一个WE8ISO8859P15编码的字符编码
所以说存到数据库中的这个编码是错误的

但是存储的时候没有报错
使用的时候它也不报错

使用时客户端要访问这个数据库中存为A4的这个字符
访问的时候oracle就问
你要访问字符，请问你的字符集是什么

因为oracle这个人很劳累命
oracle存字符的时候
它问客户端你传过来的字符它的编码是什么
oracle就根据对方的编码和自己的编码
如果一样直接存
如果不一样需要转oracle自己转
同样的
当客户端要访问字符的时候
oracle再把这个字符传给客户端以前问一下
你的字符编码是多少
对方告诉它以后
然后接着
它看一下对方的编码和自己的字符集和编码一不一样
如果不一样
它也转完了转成对方需要的编码传出去
也就是无论是存储还是访问的时候
oracle都问客户端
根据客户端的情况进行编码或直接存储
整个的字符集的转换全部在oracle端执行的

oracle一问你的字符集是多少
对方说我是utf8
实际上客户端使用字符集不是utf8
但是是NLS_LANG告诉的

oracle一看我也是uft8对方也是utf8
直接就A4就传出去了传给客户端

客户端接到A4以后
就直接把A4转成€欧元字符
没出现问题
因为客户端接到编码以后
直接根据自己的字符集把编码改成字符

客户端不关心不会考虑字符集转换
客户端接到编码
直接根据自己的字符集把它转成字符

在这种情况下是一种隐蔽的错误
在客户端无论是存储还是访问的时候
字符集字符编码都没问题

3）以前生产中常出现的错误

原来有这么种情况

oracle数据库用的是us7
客户端用的是中文16gbk
NLS_LANG设的是us7，其实客户端是中文

这个时候存中文有时候可以用
大部分情况没问题

因为我存中文的时候在客户端编好码了
往数据库存的时候
数据库一看NLS_LANG和自己的字符集一样
直接把客户端的编码存进数据库里面
访问的时候也直接访问

所以原来很早以前很多DBA犯一个错误

oracle数据库用us7，因为用的是unix，unix是英文
oracle监测到操作系统是英文以后oracle直接默认也是英文
很多人不知道字符集的概念
直接就存储到英文了，直接就设置成us7了

可能客户端用的是中文
NLS_LANG设成us7

个人在整个使用过程没有问题
但将来会有很多问题
实际上oracle存储的字符是用中文的编码存储的
但oracle字符集是us7

这是一个经典的错误

所以说一定要正确的设置字符集
数据库中存储的字符应该是数据库字符集所使用编码得到的字符的编码
这样在其它客户使用其它不同字符集的时候
不管使用哪种字符集和哪种正确的设置
才能始终保证返回数据正确性

十一）正确示例

如果字符集出问题了我们该如何去做

举个例子

1）客户端是utf8，nls_lang是utf8，oracle是gbk

现在的情况是

[oracle@redhat4 ~]$ 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=

linux服务器的字符集是utf-8

oracle的字符集可以查一下

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         10.2.0.1.0

20 rows selected.

由
NLS_CHARACTERSET ZHS16GBK
知道当前oralce的字符集是中文

如果现在我们的客户端的字符集是UTF8
服务器端数据库的字符集是GBK中文
NLS_LANG应该设成utf8

如果设成utf8的话存储是没问题的

先在客户端的sqlplus窗口执行

[oracle@redhat4 ~]$ export NLS_LANG=american_america.utf8

把客户端NLS_LANG参数设成utf8

这里实际不是给了服务器端一个参数值
是给了执行这个命令的客户端的会话一个参数值
其它会话的此参数值不变
如果发起sqlplus会话的系统用户设置了这个环境变量
它默认会采用这个用户的设置值

[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.zhs16gbk
[oracle@redhat4 ~]$ export NLS_LANG=american_america.utf8
[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.utf8

上面一句先返回的是默认值
export一个新值以后这个会话的值改变了

然后登陆数据库

[oracle@redhat4 ~]$ sqlplus / as sysdba

再执行语句

SQL> select * from t2;

no rows selected

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)

向数据库插入数据

SQL> insert into t2 values(1,'甲骨论');

1 row created.

这都是正确的

SQL>  commit;

Commit complete.

然后提交修改

接着执行

SQL> select id,name,dump(name,1016) from t2;

  ID NAME
---- ----------
DUMP(NAME,1016)
--------------------------------------------------------------------------------
   1 甲骨论
Typ=1 Len=6 CharacterSet=ZHS16GBK: bc,d7,b9,c7,c2,db

显示id是1，name是甲骨论

dump出来的数值是
Typ=1 Len=6 CharacterSet=ZHS16GBK: bc,d7,b9,c7,c2,db

把name这个列以16进制的形式直接显示出来
CharacterSet=ZHS16GBK说明字符集是16gbk

dump(name,1016)中
要得到8进制就是1008，10进制是1010
在8、10或16这个基础上加上1000出现个如1016
表示它会同时显示数据库字符集是什么
否则只显示后面的16进制数据bc,d7,b9,c7,c2,db

如：

SQL> select id,name,dump(name,16) from t2;

  ID NAME
---- ----------
DUMP(NAME,16)
--------------------------------------------------------------------------------
   1 甲骨论
Typ=1 Len=6: bc,d7,b9,c7,c2,db

bc,d7,b9,c7,c2,db是16进制
就是甲骨论的在oracle数据库里面以中文字符集显示的
这个是正确的

我们在其它客户端去访问一下
在oracle sql developer默认字符集是GBK
执行select * from t2；
显示出了正确结果“甲骨论”

客户端是utf8，nls_lang是utf8，oracle是gbk
整个的存储跟显示都没问题

刚才使用了oracle sql developer软件
sql developer 也和 sqlplus 一样
它没有字符集，也是使用操作系统

2）客户端是GBK，nls_lang也是GBK

另一个操作系统客户端是GBK
nls_lang也是GBK

它登陆上数据库以后
访问和获取的时候也正常
因为数据库存储正常
这个客户端访问也正常
只要字符集不出现显示不了的情况
都可以显示
整个过程刚展示的时候是没问题的

十二）编码造成的错误

1）存入数据库的编码是错误的

客户端是UTF8，数据库是GBK，NLS_LANG为gbk

老师刚才的展示是没问题的

这时客户端的字符集是UTF8
服务器端数据库的字符集是GBK中文

假设这时退出使用NLS_LANG为utf8的会话
将NLS_LANG改为gbk
再重新进入sqlplus

[oracle@redhat4 ~]$ export NLS_LANG=american_america.zhs16gbk
[oracle@redhat4 ~]$ sqlplus / as sysdba

这个应该有问题
因为nls_lang没有正确的显示客户端的操作系统字符集

实际是这个nls_lang和刚才输入数据“甲骨论”时使用的不一样了

SQL> select * from t2;

        ID NAME
---------- --------------------
         1 ▒׹▒▒▒

得到乱码

刚才数据库存入的是正确的gbk编码的数据
这时nls_lang是gbk
这样oracle直接把gbk编码的数据传到了sqlplus

而这里sqlplus客户端使用utf8编码显示“甲骨论”的gbk编码
所以结果“甲骨论”字符显示为乱码

再插入新字符“中国”

SQL> insert into t2 values(2,'中国');

1 row created.

SQL> commit;

Commit complete.

然后提交

SQL> select * from t2;

        ID NAME
---------- --------------------
         1 ▒׹▒▒▒
         2 中国

甲骨论显示不了了，中国可以正常显示

甲骨论是 nls_lang设为utf8时输入的

2）错误结果的原因

现在我们又加了一个客户端
客户端是utf8
但是nls_lang设成gbk了

它去取数据访问数据行的时候
服务端数据库是gbk
现在访问的时候出现乱码
甲骨论三个字显示不出来

因为“甲骨论”是以gbk形式正确的存储的没问题
结果用utf8客户端访问的时候
oracle服务器问要啥编码
客户端告诉服务器我用gbk编码
服务器一看我自己是gbk，你也要gbk
直接把gbk编码扔过来了

扔过来以后
这个客户端不知道还以为是utf编码
直接就使用utf去显示
其实它用gbk编码的
不能正常显示，这有问题

但是为什么客户端用utf8存储到服务器的
它自己存储的“中国”显示就没问题呢
存储的可以正常显示

因为很简单

客户端用utf8存储的时候
“中国”两个字用utf8正确编码了
编码以后传过来了
oracle是gbk编码，它一看，你是用什么编码的
客户端说了我用GBK编码的，实际使用utf8编码的
好服务器直接就存进去了

显示的时候也可以正常显示
但实际上服务器存的这些字符对应的编码是错误的

有个命令可以去判断错误
还是用
select id,name,dump(name,1016) from t2;

在sql developer中得到的结果是

SQL> select id,name,dump(name,1016) from t2;

        ID NAME
---------- ------------------------------------------------------------
DUMP(NAME,1016)
--------------------------------------------------------------------------------
         1 甲骨论
Typ=1 Len=6 CharacterSet=ZHS16GBK: bc,d7,b9,c7,c2,db

         2 涓浗
Typ=1 Len=6 CharacterSet=ZHS16GBK: e4,b8,ad,e5,9b,bd

实际存储的编码“甲骨论”“中国”肯定不一样

我们在oracle sql developer中再插入一行
insert into t2 values(3,’中国’);
3这个应该是正确的，然后提交一下

查一下t2，最后得到的结果是

SQL> select id,name,dump(name,1016) from t2;

        ID NAME
---------- ------------------------------------------------------------
DUMP(NAME,1016)
--------------------------------------------------------------------------------
         1 甲骨论
Typ=1 Len=6 CharacterSet=ZHS16GBK: bc,d7,b9,c7,c2,db

         3 中国
Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

         2 涓浗
Typ=1 Len=6 CharacterSet=ZHS16GBK: e4,b8,ad,e5,9b,bd

返回的3是正确的
2是错误的
2和3在存储的时候编码都不一样了

正确的“中国”的zhs16gbk的编码应该是d6,d0,b9,fa
“中国”的utf8的正确的编码是e4,b8,ad,e5,9b,bd

刚才从
select id,name,dump(name,1016) from t2;
结果看
里面出现了一个严重的错误
客户端在往oracle存数据的时候存的编码是错误的

这个错误几乎是不可逆的
因为存的就是错误的
这个是非常危险的

错误有两种

一是存储的是错误的字符编码
是刚才那种情况

还有一种情况
存储的是正确的编码
只是执行语句时客户端设错了，这个无所谓

3）存入数据库的编码是正确的

客户端是UTF8，数据库是GBK，NLS_LANG为gbk

举例讲
在sqlplus显示的时候
如

SQL> select * from t2;

        ID NAME
---------- --------------------
         1 ▒׹▒▒▒
         2 中国

甲骨论显示的是乱码
实际上甲骨论没有问题
这是因为客户端设错了

这时
sqlplus所在客户端使用utf8编码
oracle是GBK
而nls_lang设成了gbk，设错了

一般客户端对正确的编码有正确的显示
不正确的编码显示为乱码

如果你的字符集出乱码了
先判断存储的字符编码有没有问题
如果是正确的就ok了
如果是错误的就麻烦了

以后在实训里面会讲这个东西
但是都给大家讲了

十三）两个客户端软件的字符集设置

本人在实践中发现了一些客户端使用的字符集对整个转码过程的影响

1）oracle sql developer中字符集的设置

oracle sql developer是oracle自己设计生产的oracle客户端软件
这个软件 首选项 的 环境设置 中有个 编码设置 选项
有很多的编码选项，包括UTF-8和GBK等

事实上这个选项只是改变软件本身SQL工作表中输入信息的编码方式
当输入语句执行和向oracle服务器传输时
不使用这个编码选项，都使用的是数据库的编码方式值
在它的工作过程中字符集始终都不改变也改变不了

在oracle sql developer中
执行
! echo $NLS_LANG
始终没有返回值

但可以执行
select userenv(‘language’) from dual;
返回用户环境及当前会话的语言环境
就是oracle sql developer当前会话使用的值

在sql developer中执行默认得到的结果是
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

这个情况和在windows没有设置NLS_LANG
在命令提示符中运行sqlplus中执行的结果是一样的

SQL> host echo %NLS_LANG%
%NLS_LANG%

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

结果的前两部分
和oracle数据库的语言和地区设置不同
所以这两部分不是来自于oracle端

在sql developer->首选项->数据库->NLS 中
有 语言和地区 选项
可以更改
手动改变后的返回值就改变了，改变后如：
KANNADA_UNITED KINGDOM.ZHS16GBK
就是会话的语言和地区发生了改变
所以这两部分来自于这个软件本身的设置

sql developer中的 数据库 NLS 设置中没有编码的选项
只能改变前两部分，并且会影响用户环境language的值

软件的编码可以在编码选项中改变
这个选项怎么改都不会影响 客户会话 的字符集

改变oracle sql developer设置中的编码选项为任何值，就是为任何的编码

select userenv(‘language’) from dual;
返回值的编码部分都不会改变
始终都是ZHS16GBK

而且
select dump(‘中国’,1016) from dual;
的结果都是
Typ=96 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

再改变sql developer所在操作系统windows的环境变量NLS_LANG的值
对select userenv(‘language’) from dual;
的返回值没有任何的影响
这时oracle所在系统的字符集设置是utf8
所以和oracle服务器所在操作系统也没有关系

这里还是确定不了最后的字符集来自哪里
因为数据库和客户端操作系统的字符集都是16gbk
所以尝试了一下更改数据库的字符集
把数据库的字符集改为了AL32UTF8
结果不管sql developer怎么更改设置
select userenv(‘language’) from dual;
最后的字符集都是AL32UTF8
而这时客户端操作系统的字符编码仍然是16GBK

最终发现这个软件使用和传输的编码和oracle数据库是一致的
而且不用使用NLS_LANG参数

得到的结论这个软件不使用NLS_LANG参数，就是这个参数在oracle sql developer中被忽略
而且这个软件在执行语句和传输信息时使用的都是oracle数据库本身的字符集

2）putty字符集设置

在使用putty运行sqlplus时
putty的字符集设置对结果有影响

在putty的标题栏右键
选择 chang setting…
打开PuTTY Reconfiguration窗口

在Window-》Translation的设置部分
有一个Remote character set
里面有很多的字符集可供选择

这里的选择对sql语句的执行有影响

在 语句输入窗口 就是这个软件的命令行中

输入的语句字符的显示
使用这个软件的设置中的Font settings中的Font设置的字体的编码

在其中启动的sqlplus中
其中语句的执行
在向oracle服务器传递信息的时候
都使用Remote character set设置的字符集编码
并传向了服务器

在putty中运行的sqlplus使用的字符集不会影响到这个设置

即使sqlplus会话中字符集为gbk
而这里设置为utf8
最终传递到oralce服务器的数据是utf8编码方式
虽然这时sqlplus中字符集是GBK

[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.zhs16gbk

[oracle@redhat4 ~]$ sqlplus / as sysdba

会话中的字符集设置是ZHS16GBK

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

查询存入数据库的“中国”的编码

SQL> select dump('中国',1016) from dual;

DUMP('中国',1016)
-----------------------------------------------------
Typ=96 Len=6 CharacterSet=ZHS16GBK: e4,b8,ad,e5,9b,bd

结果存入数据库的是“中国”的utf8的编码

这里服务器是gbk编码
它接受到一个sql语句，里面有字符
而服务器要使用NLS_LANG确定字符的编码方式
而NLS_LANG这里设置为了gbk
但实际上传输的是uft8的编码
服务器不会知道putty的这个设置，而是参考NLS_LANG的值
这时服务器不会对这个编码转码而直接存到数据库中
这样存到数据库中的就会是这些字符的uft8编码而被当做了gbk编码
这样就存入了错误的编码

当有从服务器传至sqlplus的字符数值时
因为它得到的NLS_LANG是GBK，数据库自己也是GBK
oracle不会对它转码
PuTTY得到这个原来用utf8进行的编码后
直接使用它默认的编码这里是utf8编码显示出来
这里客户端可能会看到正确的显示结果，其实这是个隐蔽的错误
数据库存入的并不是正确的编码值

在客户端和数据库服务器传输编码时
不管中间的环节字符集编码如何设置包括NLS_LANG参数和客户端设置的编码
只要这些环节的设置不改变
在客户端输入的字符传递到数据库
不管这时的编码是不是数据库的编码也就是编码是否正确
保存到库中后
如果每个转码环节不发生错误和没有产生乱码
在通过这些环节返回时
总是会显示出原来输入的字符
因为这些环节的设置不变而且如果转码都可以成功
保存和取出执行的所有工作就是转码工作是完全对称的
所以客户可以得到和输入时一样的显示字符
即使存入数据库的字符的编码是错误的

为了解决这个putty软件字符集设置造成的错误
可以把putty的Remote character set和NLS_LANG设置为一样

下面步骤中数据库编码是ZHS16GBK
客户端sqlplus会话设置的也是ZHS16GBK编码

方法一、改变Remote character set，保留NLS_LANG

我使用的PuTTY的Remote character set中没有默认中文字符集
而且也不能手动直接输入编码方式

所以要使用曲线方式

在PuTTY Reconfiguration中的
widow->Appearance中
有一个Font setting
选择一种中文字体，如仿宋_GB2312或新宋体等
在字体设置窗口的下面会显示出这个字体的字符集
这里的中文字体都可以选择使用字符集CHINESE_GB2312

也可以选Fixedsys字体
这是windows操作系统使用的比较老的字体
它也支持字符集CHINESE_GB2312

也有其它一些字体可使用CHINESE_GB2312字符集，可酌情选择

然后在Remot character set中
选择Use font encoding
然后Apply

这时PuTTY向oracle服务器端传送的就是中文字符编码了

执行语句发现存入数据库中的字符编码正确

[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.zhs16gbk

[oracle@redhat4 ~]$ sqlplus / as sysdba

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL> select dump('中国',1016) from dual;

DUMP('中国',1016)
-----------------------------------------------
Typ=96 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

方法二、改变NLS_LANG，保留软件的Remote character set

这是个不太合理的办法

当putty的Remote character set为UTF8时
可以在PuTTY中把shell的NLS_LANG设为utf8
告诉数据库传过去的是utf8编码
这样的终端的显示结果和存入数据库的字符编码也都是正确的

但这种设置不合理
因为NLS_LANG这种方法应该告诉的是sqlplus客户端使用的字符编码
但实际却告诉的是PuTTY软件的编码
NLS_LANG一般不会用来声明终端软件的字符集设置

从前面两个最常用的oracle客户端软件的使用可以看出
现在关于字符集的设置在系统软件和客户端有些混乱
每一个环节都要弄清楚才能执行正确

十四）判断编码错误

要知道是正确的编码还是错误的编码
可以找一个正确的环境
把这个字给显示一下

比如oracle sql developer是个正确的环境

想知道“中国”这两个字的正确的编码
可以使用语句
select dump(‘中国’,1016) from dual;
返回当前环境下存入数据库的“中国”两个字符的编码

在oracle sql developer中执行的结果是：
Typ=96 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa
是正确的

而sqlplus使用的PuTTY程序字符集是utf8
sqlplus客户端NLS_LANG设为gbk时
执行的结果是

SQL> select dump('中国',1016) from dual;

DUMP('中国',1016)
-----------------------------------------------------
Typ=96 Len=6 CharacterSet=ZHS16GBK: e4,b8,ad,e5,9b,bd

这时存入数据库的是“中国”的utf8编码
而数据库是GBK编码
这个编码就是错误的

在字符集是16gbk时，对“中国”的编码
CharacterSet=ZHS16GBK: d6,d0,b9,fa是正确的编码
而e4,b8,ad,e5,9b,bd是“中国”的utf8编码，是当前不正确的编码

想知道正确的编码在一个正确的环境使用dump就行了
就是要保证目前数据库的环境是对的
有一个正确的环境就是客户和服务器字符集设置都是正确的

知道了字符正确的编码也可以判断编码环境是否正确
知道了“中国”的正确编码
判断编码执行的是否正确
在当前的环境里面
去执行select dump(‘中国’,1016) from dual;就可以知道了
数据库用的是16gbk
语句执行的时候得到的编码是
CharacterSet=ZHS16GBK: d6,d0,b9,fa
这就是正确的编码，说明编码环境是正确的

十五）语言和地区

一个数据库字符集NLS_LANG里面
除了设置字符集以外
还需要设语言和地区

如：american_america.AL32UTF8
american_america是语言和地区

1）语言

这个语言和我们显示的字符集和字符没有关系
语言就是用中文显示还是英文显示

举例讲登陆数据库以后

[oracle@redhat4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 1 11:42:36 2017

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

有很多的提示信息都是英文
是因为我们设置的NLS_LANG

[oracle@redhat4 ~]$ echo $NLS_LANG
american_america.zhs16gbk

american表示英语的意思
它只影响sqlplus或者说这个工具中oracle给的反馈信息
一些消息、一些错误提示是用英文还是用中文

只在只有这一个地方有意义
别的都没有意义

2）在windows命令提示符中使用sqlplus

老师的演示后面用了windows的命令提示符登录
这里我的实验环境在客户端操作系统windows没有安oracle客户端

执行时提示

C:\WINDOWS>sqlplus hr/hr@jiagulun
‘sqlplus’ 不是内部或外部命令，也不是可运行的程序
或批处理文件。

说明当前环境无法执行sqlplus命令
是没有安装或者命令提示符找不到这个命令
我这里是因为在客户端系统中没有安装sqlplus
所以先安装一下

在oracle官方网站下载这两个包
instantclient-basic-win32-11.2.0.1.0.zip
和
instantclient-sqlplus-win32-11.2.0.1.0.zip
把它们解压到同一目录
我们就有了sqlplus和它执行需要的环境

这两个包oracle的一再的升级出了很多的版本
自己选择，可以用就行了

然后在客户端windows的环境变量path中把sqlplus所在的目录加上

我把这两个包解压后放到了
C:\instantclient_11_2目录
所以要在path中加上C:\instantclient_11_2这个路径
命令行要执行sqlplus时就可以找到这个命令了

3）语言的影响

打开windows的命令提示符
执行sqlplus
因为这时的sqlplus运行的环境还没有完全设置到
所以设置还存在些问题，会返回错误信息

这里先将NLS_LANG的语言设置为SIMPLIFIED CHINESE

C:\WINDOWS>SET NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

C:\WINDOWS>set NLS_LANG
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

然后执行sqlplus

C:\WINDOWS>sqlplus hr/hr@jiagulun

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 10月 2 08:56:23 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS: 无法解析指定的连接标识符


请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误


请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误


SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus

返回的错误信息是简体中文的

然后改变一下语言
把语言设为american

C:\WINDOWS>set NLS_LANG=american_america.ZHS16GBK

C:\WINDOWS>set NLS_LANG
NLS_LANG=american_america.ZHS16GBK

再执行一下sqlplus

C:\WINDOWS>sqlplus hr/hr@jiagulun

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 2 09:04:21 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

现在返回的错误信息都是英文的
和前面中文的返回错误信息内容是一样的，但是使用的语言不同

4）纠正这个返回错误

这里出现的这个错误是因为安装的客户端没有提供网络服务名的配置文件
可以从oracle服务器复制一份
也可以按指定格式和当前的数据库数据自己编写一个tnsnames.ora文件
然后放到前面安装的客户端主目录下的/network/admin/目录下
可以解决上面的错误问题

我这里windows中sqlplus客户端主目录是C:\instantclient_11_2\
我从oracle服务器的$ORACLE_HOME/network/admin目录下
复制了一份tnsnames.ora到
客户端的C:\instantclient_11_2\network\admin\目录下

在命令提示符下再次执行sqlplus

C:\WINDOWS>sqlplus hr/hr@jiagulun

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 2 09:29:08 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

登陆成功

这里只是为了看oracle返回的错误信息是英文的还是中文的
重点不是解决这个错误，不细说了

NLS_LANG中的american只影响oracle反馈的一些信息或者一些错误提示
使用英文或中文
和实际上你输入数据库的字符是英文和中文没有任何的关系

5）地区

NLS_LANG的第二部分america是表示地区
表示美国地区，这是英文是美国地区

oracle显示里面除了显示错误信息以外
还有比如日期格式、比如货币符号等等这些
受地区影响，受america影响
就是说我们的货币符号和一些日期显示就会随美国人

NLS_LANG前两部分无关大局
主要是第三部分这个字符集

十六）相关的一些语句

还有一些大家可以自己去演示一下

比如
select userenv(‘language’) from dual;
大家可以回去看一下

在windows命令提示符下
默认状态

C:\Documents and Settings\Administrator>set NLS_LANG
环境变量 NLS_LANG 没有定义

进入sqlplus

C:\Documents and Settings\Administrator>sqlplus sys/oracle@jiagulun as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 15 11:22:30 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


???:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

在sqlplus中查一下NLS_LANG的设置

SQL> host set NLS_LANG
环境变量 NLS_LANG 没有定义

仍然没有值

没有设置NLS_LANG参数时执行

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

这个返回的值是当前sqlplus会话的参数值
是返回执行这个语句所在会话使用的NLS相关的值

从下面的实验验证了前面已经说过的一个问题

如果为打开命令提示符的会话设置了环境变量并重新登陆数据库

C:\Documents and Settings\Administrator>set NLS_LANG=american_china.ZHS16GBK

C:\Documents and Settings\Administrator>sqlplus sys/oracle@jiagulun as sysdba

在sqlplus中查一下NLS_LANG的设置

SQL> host set NLS_LANG
NLS_LANG=american_china.ZHS16GBK

这时有值，因为这个会话中给NLS_LANG参数set了值

然后查看环境

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_CHINA.ZHS16GBK

这时返回了这个会话设置的值

这时把oracle数据库的字符集更改为AL32UTF8
改变过程使用了
ALTER DATABASE character set INTERNAL_USE AL32UTF8;
语句
过程有些复杂不一一说明了

再在客户端windows命令提示符中重复执行上面的命令

C:\Documents and Settings\Administrator>set NLS_LANG=american_china.ZHS16GBK

C:\Documents and Settings\Administrator>set NLS_LANG
NLS_LANG=american_china.ZHS16GBK

进入sqlplus

C:\Documents and Settings\Administrator>sqlplus sys/oracle@jiagulun as sysdba

再次查询

SQL> host set NLS_LANG
NLS_LANG=american_china.ZHS16GBK

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
---------------------------------------------

AMERICAN_CHINA.AL32UTF8

这时的用户会话中
前两部分是NLS_LANG的设置值
而字符集明显的是数据库的字符集

结论：
windows命令提示符中执行sqlplus
NLS的语言和地区来自此会话的客户端环境
包括NLS_LANG参数和客户端操作系统的值
第三部分的编码始终和数据库的设置一样
还是那个感觉NLS编码值和NLS_LANG这个参数在不同软件使用时有些混乱

2017年10月22日
文字：韵筝