ORACLE字符集问题探解

 Linux中通过locale来设置字符集

在Linux中通过locale来设置程序运行的不同语言环境，locale由ANSI C提供支持。locale的命名规则为<语言>_<地区>.<字符集编码>，如zh_CN.UTF-8，zh代表中文，CN代表大陆地区，UTF-8表示字符集。在locale环境中，有一组变量，代表国际化环境中的不同设置：

1.    LC_COLLATE
定义该环境的排序和比较规则

2.    LC_CTYPE
用于字符分类和字符串处理，控制所有字符的处理方式，包括字符编码，字符是单字节还是多字节，如何打印等。是最重要的一个环境变量。

3.    LC_MONETARY
货币格式

4.    LC_NUMERIC
非货币的数字显示格式

5.    LC_TIME
时间和日期格式

6.    LC_MESSAGES
提示信息的语言。另外还有一个LANGUAGE参数，它与LC_MESSAGES相似，但如果该参数一旦设置，则LC_MESSAGES参数就会失效。LANGUAGE参数可同时设置多种语言信息，如LANGUANE="zh_CN.GB18030:zh_CN.GB2312:zh_CN"。

7.    LANG
LC_*的默认值，是最低级别的设置，如果LC_*没有设置，则使用该值。类似于 LC_ALL。

8.    LC_ALL
它是一个宏，如果该值设置了，则该值会覆盖所有LC_*的设置值。注意，LANG的值不受该宏影响。

一个例子：

设置前，使用默认locale：
debian:~# locale
LANG="POSIX"
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

设置后，使用zh_CN.GDK中文locale：

debian:~# export LC_ALL=zh_CN.GBK
debian:~# locale
LANG=zh_CN.UTF-8
LC_CTYPE="zh_CN.GBK"
LC_NUMERIC="zh_CN.GBK"
LC_TIME="zh_CN.GBK"
LC_COLLATE="zh_CN.GBK"
LC_MONETARY="zh_CN.GBK"
LC_MESSAGES="zh_CN.GBK"
LC_PAPER="zh_CN.GBK"
LC_NAME="zh_CN.GBK"
LC_ADDRESS="zh_CN.GBK"
LC_TELEPHONE="zh_CN.GBK"
LC_MEASUREMENT="zh_CN.GBK"
LC_IDENTIFICATION="zh_CN.GBK"
LC_ALL=zh_CN.GBK

"C"是系统默认的locale，"POSIX"是"C"的别名。所以当我们新安装完一个系统时，默认的locale就是C或POSIX。

在Debian中安装locales的方法如下：

· 通过apt-get install locales命令安装locales包
· 安装完成locales包后，系统会自动进行locale配置，你只要选择所需的locale，可以多选。最后指定一个系统默认的locale。这样系统就会帮你自动生成相应的locale和配置好系统的locale。

· 增加新的locale也很简单，用dpkp-reconfigure locales重新配置locale即可。

· 我们也可手动增加locale，只要把新的locale增加到/etc/locale.gen文件中，再运行locale-gen命令即可生成新的locale。再通过设置上面介绍的LC_*变量就可设置系统的locale了。下是一个locale.gen文件的样例。

· # This file lists locales that you wish to have built. You can find a list
· # of valid supported locales at /usr/share/i18n/SUPPORTED. Other
· # combinations are possible, but may not be well tested. If you change
· # this file, you need to rerun locale-gen.
· #
·zh_CN.GBK GBK
·zh_CN.UTF-8 UTF-8

影响oracle数据库字符集最重要的参数是NLS_LANG参数。它的格式如下:
NLS_LANG = language_territory.charset
它有三个组成部分(语言、地域和字符集)，每个成分控制了NLS子集的特性。其中:
Language 指定服务器消息的语言，territory 指定服务器的日期和数字格式，charset 指定字符集。
如:AMERICAN _ AMERICA. ZHS16GBK,SIMPLIFIED CHINESE_CHINA.ZHS16GBK
从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。

如何查询Oracle的字符集
1、查询oracle server端的字符集
有很多种方法可以查出oracle server端的字符集，比较直观的查询方法是以下这种:
select userenv(‘language’) from dual;
2、如何查询dmp文件的字符集
用oracle的exp工具导出的dmp文件也包含了字符集信息，dmp文件的第2和第3个字节记录了dmp文件的字符集。如果dmp文件不大，比如只有几M或几十M，可以用UltraEdit打开(16进制方式)，看第2第3个字节的内容，如0354，然后用以下SQL查出它对应的字符集:
SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;
    ZHS16GBK
如果dmp文件很大，比如有2G以上(这也是最常见的情况)，用文本编辑器打开很慢或者完全打不开，可以用以下命令(在unix主机上):
cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6   
然后用上述SQL也可以得到它对应的字符集。
3、查询oracle client端的字符集
这个比较简单。在windows平台下，就是注册表里面相应OracleHome的NLS_LANG。还可以在dos窗口里面自己设置，比如:
set nls_lang=AMERICAN_AMERICA.ZHS16GBK
在unix平台下，就是环境变量NLS_LANG
$echo $NLS_LANG

    AMERICAN_AMERICA.ZHS16GBK
   
select nls_charset_name(to_number('00B2','xxxx')) from dual;
WE8MSWIN1252
select nls_charset_name(to_number('0354','xxxx')) from dual;
ZHS16GBK
select nls_charset_name(to_number('0367','xxxx')) from dual;
UTF8

三、修改oracle的字符集
上文说过，oracle的字符集有互相的包容关系。如us7ascii就是zhs16gbk的子集,从us7ascii到zhs16gbk不会有数据解释上的问题,不会有数据丢失。在所有的字符集中utf8应该是最大,因为它基于unicode,双字节保存字符(也因此在存储空间上占用更多)。

　　一旦数据库创建后，数据库的字符集理论上讲是不能改变的。因此，在设计和安装之初考虑使用哪一种字符集十分重要。根据Oracle的官方说明，字符集的转换是从子集到超集受支持,反之不行。如果两种字符集之间根本没有子集和超集的关系，那么字符集的转换是不受oracle支持的。对数据库server而言，错误的修改字符集将会导致很多不可测的后果，可能会严重影响数据库的正常运行，所以在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说，除非万不得已，我们不建议修改oracle数据库server端的字符集。特别说明，我们最常用的两种字符集ZHS16GBK和ZHS16CGB231280之间不存在子集和超集关系，因此理论上讲这两种字符集之间的相互转换不受支持。

　　1、修改server端字符集(不建议使用)

　　在oracle 8之前，可以用直接修改数据字典表props$来改变数据库的字符集。但oracle8之后，至少有三张系统表记录了数据库字符集的信息，只改props$表并不完全，可能引起严重的后果。正确的修改方法如下:

　　$sqlplus /nolog

　　SQL>conn / as sysdba;

　　若此时数据库服务器已启动，则先执行SHUTDOWN IMMEDIATE命令关闭数据库服务器，然后执行以下命令:

　　SQL>STARTUP MOUNT;

　　SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;

　　SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

　　SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;

　　SQL>ALTER DATABASE OPEN;

　　SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;

　　SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK;

　　SQL>SHUTDOWN IMMEDIATE;

　　SQL>STARTUP

        SQL> alter database character set zhs16gbk;
        alter database character set zhs16gbk
        *
        ERROR at line 1:
        ORA-12712: new character set must be a superset of old character set
        SQL> ALTER DATABASE character set INTERNAL_USE zhs16gbk;
        SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
        # 使用INTERNAL_USE可以跳过超集的检查,ALTER DATABASE character set INTERNAL_USE

　　2、修改dmp文件字符集

　　上文说过，dmp文件的第2第3字节记录了字符集信息，因此直接修改dmp文件的第2第3字节的内容就可以‘骗’过oracle的检查。这样做理论上也仅是从子集到超集可以修改，但很多情况下在没有子集和超集关系的情况下也可以修改，我们常用的一些字符集，如US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK基本都可以改。因为改的只是dmp文件，所以影响不大。

　　具体的修改方法比较多，最简单的就是直接用UltraEdit修改dmp文件的第2和第3个字节。比如想将dmp文件的字符集改为ZHS16GBK，可以用以下SQL查出该种字符集对应的16进制代码:

　　SQL> select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;

　　0354

　　然后将dmp文件的2、3字节修改为0354即可。

　　如果dmp文件很大，用ue无法打开，就需要用程序的方法了。网上有人用java存储过程写了转换的程序(用java存储过程的好处是通用性教好，缺点是比较麻烦)。我在windows下测试通过。但要求oracle数据库一定要安装JVM选项。