Oracle 字符集，想说爱你不容易
前段时间碰到两个数据库字符集不同通过 DBLINK 互导的问题，但一直没有解决，今天抽了点时间再看了下字符集的原理，虽然对字符集大致有点把握了，可问题还是无法解决，郁闷。
大致情况：从 DPLIV 数据库中把数据导入 SDB，其中，DPLIV 字符集为WE8ISO8859P1，SDB 则为 ZHS16GBK。因为只是几个小表的数据互导，不想用 imp/exp 那么麻烦，想直接建个 DBLINK 就把数据导过来，可不成功。
不成功的原因及字符集的原理：
Oracle 的语言和字符集设定分为两部分，一部分是服务器端的字符集，一部分是客户端的字符集。注：即便在服务器上通过 sqlplus 连接同一台服务器上的 Oracle 服务，仍然被视为是客户端连接服务器端。
服务器端的字符集：
服务器端的字符集是在创建数据库的时候设定的，如图：
数据库创建以后，如果需要修改字符集，通常需要重建数据库，通过导入导出的方式来转换。但也可以用一些特殊的方法来转换，详见 Eygle 大师的讲解：http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm
注：正如大师所说，更改字符集尽量要使用正常的途径，不要把你的数据库置于危险的境地。
查看数据库字符集的方法：
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Jan 23 16:53:54 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> conn has/sdbadmin@sdb
Connected.
SQL> set wrap off
SQL> set linesize 500
SQL> select * from sys.props$;
rows will be truncated
NAME VALUE$
------------------------------ -------------------------------------------
DICT.BASE 2
DBTIMEZONE 0:00
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RRRR-MM-DD-HH24:MI
NLS_DATE_LANGUAGE AMERICAN
NAME VALUE$
------------------------------ -------------------------------------------
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 TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET ZHS16GBK
NLS_RDBMS_VERSION 8.1.7.0.0
GLOBAL_DB_NAME SDB.BIS.SWIREBEV.COM
EXPORT_VIEWS_VERSION 8
22 rows selected.
SQL>
可以看到 NLS_LANGUAGE NLS_TERRITORY NLS_CHARACTERSET 等主要信息，另外也可以查询 nls_database_parameters，它也是来源于 props$ 的。
此外，nls_instance_parameters（来源于 v$parameter）中的参数可以通过参数文件来设定，它指明了客户端连接上来时默认的字符集，如果客户端注册表、环境变量中都没有指定字符集以及语言的话，就按照 nls_instance_parameters 中的参数来设定（注：但它只在服务器端，不影响客户端的设置），否则，客户端的信息会覆盖 nls_instance_parameters 中的参数。
客户端的字符集：
客户端的字符集由注册表、环境变量 NLS_LANG、alter session 来设置，其中优先级为 alter session > NLS_LANG > 注册表。
查看连接时客户端的字符集设置：
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RRRR-MM-DD
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
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 TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET ZHS16GBK
NLS_COMP BINARY
17 rows selected.
SQL>
也可以使用一下方法：
SQL> select * from nls_session_parameters;
PARAMETER VALUE
------------------------------------------------------------ --------------------------NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RRRR-MM-DD
NLS_DATE_LANGUAGE AMERICAN
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 TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
15 rows selected.
nls_session_parameters 来源于 v$nls_parameters，他指示了那些可以使用 alter session set 来设置的 nls 参数，可以看到 NLS_CHARACTERSET 和 NLS_NCHAR_CHARACTERSET 不在其中，所以这两个参数只能通过注册表或者 nls_lang 环境变量来设置，如果使用 alter session 会出现以下错误：
alter session set NLS_CHARACTERSET='WE8ISO8859P1'
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL>
注册表和环境变量 NLS_LANG 的设置：
NLS_LANG 可以分为三部分来设置 LANGUAGE_TERRITORY.CHARACTERSET，如果不设置，默认时 LANGUAGE=AMERICAN，而 TERRITORY 和 CHARACTERSET 默认是根据 LANGUAGE 来决定的，所以如果不设置 NLS_LANG，默认的 NLS_LANG = American_America.US7ASCII。
乱码的生成：
当客户端和服务器端的字符集不同时，则需要转换，而有些字符集之间无法正确转换，而造成部分信息丢失，而产生乱码。
我的问题：
我需要从 DPLIV 中把数据导到 SDB 中，而两者字符集不同，当创建好 DBLINK 之后，必须把 NLS_LANG 设置为和 DPLIV 相同：set nls_lang=AMERICAN_AMERICA.WE8ISO8859P1 此时可以正确显示中文，但通过 DBLINK 将数据插入 SDB 的时候由于 NLS_LANG 的设置和 SDB 中不同，导致插入的数据为乱码。真个过程必须分为两步才能正确完成，因此在我看来只能先设置 nls_lang=AMERICAN_AMERICA.WE8ISO8859P1，然后用 EXP 导出，再修改 DMP 文件头信息，再将 NLS_LANG 设置为 AMERICAN_AMERICA.ZHS16GBK，再导入 SDB，详见：http://www.eygle.com/special/NLS_CHARACTER_SET_04.htm
因此问题还是无法解决，不知道是不是我理解上还有问题，或有其他什么方法。
参考：
http://www.fors.com/orasupp/rdbms/nls/15389_1.HTM
http://www.eygle.com/index-special.htm（字符集专题）
http://oracle.chinaitlab.com/induction/37776.html
http://book.77169.com/data/web6103/20050228/20050228__3168073.html
http://www.bc-cn.net/Article/sjk/oracle/200506/805.html
Posted by Sky at 04:00 PM | Permalink | comments(1) | Edit | Database
Comments
没错 最后一段你的理解是错误的.
Commented by chriswong | June 5, 2008 10:38 AM