Oracle中的语言、地区与字符集

Oracle中的语言地区与字符集

1、语言、地区和字符集

使用下面的语句查询语言、地区和字符集：

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

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

此例中，AMERICAN为语言支持，AMERICA为地区支持，WE8MSWIN1252为字符集

1.1 语言支持

语言支持使用下面三个参数：

NLS_LANGUAGE：语言支持，它决定了错误消息的语言
NLS_DATE_LANGUAGE：它用于日期与月份
NLS_SORT：它决定了按何种语言进行排序

可以通过下面的语句查询这些参数的可选值：

select * from v$nls_valid_values where parameter='LANGUAGE';

常用的值是AMERICAN、SIMPLIFIED CHINESE。

select * from v$nls_valid_values where parameter='SORT';

如果客户端SQLPLUS中提示信息显示为？？？，我们可以设置NLS_LANG，例如在windows中：

set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

1.2 地区支持

地区支持使用下面的参数：

NLS_TERRITORY：地区支持
NLS_CURRENCY：货币符号
NLS_DUAL_CURRENCY：当前地区支持的二级货币符号
NLS_ISO_CURRENCY：ISO地区货币符号
NLS_DATE_FORMAT：date数据类型使用的格式
NLS_NUMERIC_CHARACTERS：小数点分隔符与组分隔符
NLS_TIMESTAMP_FORMAT：timestamp数据类型的格式
NLS_TIMESTAMP_TZ_FORMAT：timestamp with local timezone数据类型使用的格式

可以通过下面的语句查询NLS_TERRITORY参数的可选值：

select * from v$nls_valid_values where parameter='TERRITORY';

1.3 字符集

1.3.1 字符集与国家字符集

在创建数据库时，会要求选择字符集和国家字符集，它们的作用如下：

字符集用来编码CHAR,VARCHAR2,CLOB,LONG等类型的数据。
国家字符集用来编码NCHAR,NVARCHAR2,NCLOB等类型的数据。

它们对应的NLS参数分别为：

NLS_CHARACTERSET ，NLS_NCHAR_CHARACTERSET

字符集在创建数据库时确定，理论上不可以再修改，因此选择时一定要谨慎。

Oracle公司建议所有新的部署使用AL32UTF8字符集。

1.4 查询数据库中的NLS参数

全球化支持是指语言地区与字符集的相关参数的设置。我们可以查询这些设置。

注意nls_database_parameters视图查询的是数据库级别的全球化支持设置，下一节介绍全球化支持的级别。

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               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
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

2、全球化支持的级别

2.1 共有5个级别

它们分别是：数据库、实例、客户端环境、会话、语句。

它们的优先级按照上面的顺序依次递增。例如数据库语言0是American，但是客户端可以配置别的语言。

我们可以通过下面的视图查询NLS配置：

NLS_DATABASE_PARAMETERS：查询数据库的NLS配置。

NLS_INSTANCE_PARAMETERS：查询实例的NLS配置。

NLS_SESSION_PARAMETERS：查询会话的NLS配置。

2.2 配置NLS

一般我们仅仅在客户端环境、会话或是语句级别配置NLS。

客户端环境可以通过配置环境变量或者注册表来设置NLS。

会话级别可以通过alter session语句来设置NLS：

alter session set nls_date_format='dd.mm.yyyy';

语句级别配置NLS一般是通过SQL语句中调用的函数的参数来定义的。

2.3 配置NLS的应用

在查询statpack快照时，时间显示不完整，看不到小时信息：

SQL> select snap_id,snap_time from stats$snapshot;

   SNAP_ID SNAP_TIME
---------- ---------
         3 23-FEB-12
         7 23-FEB-12
         9 23-FEB-12
        11 23-FEB-12
        20 24-FEB-12
        21 24-FEB-12
        ...
        
我们可以先退出SQLPLUS，使用下面的命令设置环境变量，这种方法设置的是客户端NLS配置：

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

再次执行查询：

SQL> select snap_id,snap_time from stats$snapshot;

   SNAP_ID SNAP_TIME
---------- -------------------
         3 2012-02-23 11:00:06
         7 2012-02-23 15:00:04
         9 2012-02-23 17:00:04
        11 2012-02-23 19:00:03
        20 2012-02-24 04:00:02
        21 2012-02-24 05:00:03
        ...
        
这时候已经可以了，另外在Windows环境中，类似的语句为set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'。

另外我们也可以直接在SQLPLUS中配置session级别的设置，语句如下：

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

3、字符集对DataPump的影响

在使用imp命令导入数据时，会涉及到三个字符集：

目标数据库字符集

dump文件字符集

导入命令客户端字符集

通常情况下，我们需要确保这三个字符集一致以保证导入过程不会报错，并且不会产生乱码。

3.1 目标数据库字符集

见上文。

3.2 设置dump文件导出字符集

在exp命令执行日志中，开头部分可以看到类似下面的语句：

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

代表使用ZHS16GBK这个字符集进行导出。

测试：

首先直接导出数据库：

数据库的字符集为WE8MSWIN1252：

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

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

执行导出命令：

[oracle@rman-auxiliary ~]$ exp system/oracle file=exp.dmp log=exp.log full=y

Export: Release 11.2.0.1.0 - Production on Fri Dec 26 15:17:10 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

可以看到exp以US7ASCII进行导出，而数据库的字符集为WE8MSWIN1252，此时exp时做了"可行的字符集转换";

我们改变导出环境的NLS_LANG设置，再次执行导出：

[oracle@rman-auxiliary ~]$ export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
[oracle@rman-auxiliary ~]$ exp system/oracle file=exp.dmp log=exp.log full=y

Export: Release 11.2.0.1.0 - Production on Fri Dec 26 15:22:50 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

可以看到此时以WE8MSWIN1252字符集导出。


3.3 导入命令客户端字符集

重新创建到服务器的会话，此时NLS_LANG环境变量被重置，再执行导入操作可以看到下面的日志：

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)

此时又使用US7ASCII字符集进行导入，数据库和导出客户端的字符集都是WE8MSWIN1252，因此又做了"可行的字符集转换"。

同样，我们设置NLS_LANG环境变量：

[oracle@rman-auxiliary ~]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8MSWIN1252

再次执行导入时可以看到下面的结果：

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

没有再做"可行的字符集转换"了。

4、改变数据库的字符集

应该在创建数据库时谨慎选择字符集，不建议之后修改字符集。

要修改字符集只能从子集改到超集，反之不可。即使从子集转化为超集，也不能确保不丢是数据。

使用下面的实用程序，生成转化字符集可能引起的问题的报告：

csscan system/password full=y tochar=utf8

在使用该程序之前，必须要先执行脚本csminst.sql

然后使用下面的语句：

alter database character set ZHS16GBK;

alter database national character set UTF8;

这里新字符集必须为超集，如果想跳过检查使用INTERNAL_USE：

ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

之前需要将数据库已restrict打开：

startup restrict;

如果是windows环境，可能需要改变注册表：

HKEY_LOCAL_MACHINE->ORACLE->KEY_OraDb11g_home1->ORA_OTP_AUTOSTART值为false

如果非要改变字符集，之前的备份是最重要的。