Exp导出的数据dmp文件中的第2,3字节表示导出数据时的OS字符集ID(NLS_LANG环境变量保存)
下图中:
①表示目标OS的字符集
②表示目标DB的字符集
③表示目标DB的NCHAR字符集
④表示源OS的字符集
⑤表示源DB的NCHAR字符集
下图例子中,导出时OS字符集为ZHT16MSWIN950,目标OS字符集US7ASCII,导入后出现乱码。
将目标OS字符集①改为”AMERICAN_AMERICA.UTF8”或”TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950”导入后正常。
此例中导出的数据文件在使用exp导出时,没有将OS字符集设定为和DB一致(最好在exp时将OS字符集NLS_LANG设定为和DB一致),但” ZHT16MSWIN950”字符集正常解析了该DB中的所有字符,imp导入时要保证目标OS能正确解析数据文件中的所有字符,方法是设定目标OS的字符集与数据文件字符集(源OS字符集)一致,或为其超集。
-------------------------------例子------------------------------------------
[oracle@hi0-ibmsv508 u01]$ imp
Import: Release 10.2.0.1.0 - Production on Mon Apr 7 14:59:45 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: kevin
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Import file: expdat.dmp > CCIGPM.DMP
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.01.00 via conventional path
Warning: the objects were exported by CCIGPM, not by you
import done in ①US7ASCIIcharacter set and③AL16UTF16NCHAR character set
import server uses ②AL32UTF8character set (possible charset conversion)
export client uses ④ZHT16MSWIN950character set (possible charset conversion)
export server uses ⑤UTF8NCHAR character set (possible ncharset conversion)
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >------------------------------------------------------------------------------------------------------------