字符集问题的初步探讨(四)

字符集问题的初步探讨(四) link:

http://www.eygle.com/special/NLS_CHARACTER_SET_04.htm

4．导入导出及转换

导入导出是我们常用的一个数据迁移及转化工具，因其导出文件具有平台无关性，所以在跨平台迁移中，最为常用。

在导出操作时，非常重要的是客户端的字符集设置，也就是客户端的NLS_LANG设置。

NLS_LANG参数由以下部分组成:

NLS_LANG=<Language>_<Territory>.<Clients Characterset>

E:\>chcp

活动的代码页: 936

E:\>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBKE:\>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on 星期六 11月 1 22:51:59 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.连接到:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select sysdate from dual;SYSDATE----------01-11月-03已选择 1 行。SQL> exit从Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Production中断开E:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:\>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 22:52:24 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select sysdate from dual;SYSDATE---------01-NOV-031 row selected.SQL>

查看客户端NLS_LANG设置可以使用以下方法:

Windows使用:echo %NLS_LANG%如:E:\>echo %NLS_LANG%AMERICAN_AMERICA.ZHS16GBKUnix使用:env|grep NLS_LANG如:/opt/oracle>env|grep NLS_LANGNLS_LANG=AMERICAN_CHINA.ZHS16GBKWindows客户端设置,可以在注册表中更改NLS_LANG,具体键值位于:HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExxxx指存在多个ORACLE_HOME时系统编号。

导入和导出是客户端产品，同SQL*PLUS和Oralce Forms一样，因此，使用EXP/IMP工具将按照NLS_LANG定义的方式转换字符集。

E:\nls2>set NLS_LANG=AMERICAN_AMERICA.US7ASCII设置导入session NLS_LANG为US7ASCIIE:\nls2>e:\oracle\ora8i\bin\imp eygle/eygle file=Sus7ascii-Cus7ascii-exp817.dmp fromuser=eygle touser=eygle tables=test这个导出文件是从US7ASCII数据库导出，导出客户端NLS_LANG也是US7ASCIIImport: Release 8.1.7.1.1 - Production on Fri Nov 7 00:59:22 2003(c) Copyright 2000 Oracle Corporation. All rights reserved.Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - ProductionWith the Partitioning optionJServer Release 8.1.7.1.1 - Production这时导入，在DMP文件和NLS_LANG之间不需要进行字符集转换。Export file created by EXPORT:V08.01.07 via conventional pathimport done in US7ASCII character set and ZHS16GBK NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion)export server uses UTF8 NCHAR character set (possible ncharset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.

E:\nls2>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK导入Session字符集设置为ZHS16GBK导入US7ASCII的导出文件E:\nls2>e:\oracle\ora8i\bin\imp eygle/eygle file=Sus7ascii-Cus7ascii-exp817.dmp fromuser=eygle touser=eygleImport: Release 8.1.7.1.1 - Production on Fri Nov 7 00:38:55 2003(c) Copyright 2000 Oracle Corporation. All rights reserved.Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - ProductionWith the Partitioning optionJServer Release 8.1.7.1.1 - ProductionIMP-00016: required character set conversion (type 1 to 852) not supportedIMP-00000: Import terminated unsuccessfully在从导出文件US7ASCII到导入 NLS_LANG设置为ZHS16GBK的过程中，不支持单Byte字符集向多Byte转换，报出以上错误。

Export file created by EXPORT:V08.01.07 via conventional pathimport done in US7ASCII character set and ZHS16GBK NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion)export server uses UTF8 NCHAR character set (possible ncharset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.这时候经过第一步转换后的数据，US7ASCII到ZHS16GBK丢失首位，原样插入数据库，我们看到这时数据库中存放的就是错误的字符(在后面

部分我们做了详细的转换):E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Fri Nov 7 00:35:39 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle8i Enterprise Edition Release 8.1.7.1.1 - ProductionWith the Partitioning optionJServer Release 8.1.7.1.1 - ProductionSQL> select * from test;NAME--------------------2bJTtest