系统和数据库LANG不一致的问题

对于系统和数据库字符集设置不一致的问题，可能会出现多种问题.
1.对数据imp/exp导出导入时，可能会有些字符出现报错,典型的报错如下(虽然对数据的影响应该不会造成影响):
EXP-00091:Exporting questionable statistics.
2.有些数据格式可能不能识别，遇到一个问题，如果系统shell设置的环境变量不是AL32UTF8，则有些数据格式的数据无法识别
主要是影响到date数据类型.

如果系统shell环境变量为中文，数据库环境变量为字符类型，则会报错
$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
$ sqlplus "/ as sysdba"

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8

SQL> insert into testxx values(to_date('13-Jan-10 17:00:52', 'DD-MM-YY HH24:MI:SS'));
insert into testxx values(to_date('13-Jan-10 17:00:52', 'DD-MM-YY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

如果系统shell环境变量为中文，数据库环境变量为中文，也会报错
[oracle:/home/oracle#]env|grep LANG
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG=en_US.UTF-8

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

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

SQL> insert into testxx values(to_date('13-Jan-10 17:00:52', 'DD-MM-YY HH24:MI:SS'));
insert into testxx values(to_date('13-Jan-10 17:00:52', 'DD-MM-YY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

而如果环境变量中的NLS_LANG设置为AL32UTF8类型的话，无论数据库的LANGUAGE设置为AL32UTF8或ZHS16GBK，上述的sql均能正常执行.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23850820/viewspace-1035419/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-1035419/