dmp文件字符集受客户端session字符集环境变量NLS_LANG影响

今天迁移公司的客户关系数据库，正好碰到了字符集的问题，做了个实验验证dmp文件的字符集受什么影响，实验如下：

本机系统windows 7 64bit

修改客户端session字符集环境变量NLS_LANG

set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

导出dmp

exp scott/tiger@server1 file=scott_gbk.dmp log=scott_gbk.log

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...

………………

根据日志Export done in ZHS16GBK character set可看到导出字符集与刚才我们设置的客户端session环境变量一样；

也可使用UltraEdit查看导出的dmp文件（第一列第2,3字节代表字符集）是哪种字符集。

修改客户端session字符集环境变量NLS_LANG再做验证：

set NLS_LANG=AMERICAN_AMERICA.we8iso8859p1

导出dmp

exp scott/tiger@server1 file=scott_we8.dmp log=scott_we8.log

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and UTF8 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT

根据日志Export done in WE8ISO8859P1 character set可看到导出字符集与刚才我们新设置的客户端session环境变量一样；

也可使用UltraEdit查看 DMP文件。

综上：

客户端session字符集环境变量直接影响dmp的字符集，所以先设置好客户端session环境变量(最好是与Oracle Server相同，如果字符集是Oracle Server的子集，可能会丢失数据)，再做dmp导出是比较保险的做法。

[@more@]

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

转载于:http://blog.itpub.net/24586480/viewspace-1050888/