Linux查看imp文件夹,Oracle 查看imp导出文件中字符集

我们在exp导入的时候，经常会有警告字符不一致的问题。下面来看看在imp/exp导入导出的时候，nls_lang这个参数到底应该怎么来配置。

Oracle推荐在执行exp的时候nls_lang配置与database nls_character一致，这样不会出现字符转换的问题，能提高exp的效率。查看database nls_characterSQL>selectvaluefromnls_database_parameterswhereparameter='NLS_CHARACTERSET';

VALUE

------------------------------

ZHS16GBK

我们这里只做查看已经导出的exp文件的字符集。$ echo $NLS_LANG

$

$ exp userid=scott/oracle file='/tmp/scott.dmp'log='/tmp/scott.log'

Export: Release 11.2.0.3.0 - Production onThu Aug 30 13:07:22 2012

Copyright (c) 1982, 2011, Oracle and/orits affiliates.Allrights reserved.

Connected to: OracleDatabase11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Withthe Partitioning, OLAP, Data MiningandRealApplication Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

. exporting pre-schemaprocedural objectsandactions

. exporting foreignfunctionlibrary namesforuserSCOTT

. exporting PUBLICtype synonyms

. exporting private type synonyms

. exporting object type definitions foruserSCOTT

About toexport SCOTT's objects ...

. exporting databaselinks

. exporting sequencenumbers

. exporting cluster definitions

. about toexport SCOTT's tables via Conventional Path ...

. . exporting tableBONUS          0rowsexported

EXP-00091: Exporting questionable statistics.

. . exporting tableDEPT          4rowsexported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

. . exporting tableEMP         14rowsexported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

. . exporting tableSALGRADE          5rowsexported

EXP-00091: Exporting questionable statistics.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional andextensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups andchildren

. exporting dimensions

. exporting post-schemaprocedural objectsandactions

. exporting statistics

Export terminated successfully withwarnings.

从上面提示可以看到database的字符集是ZHS16GBK，而我的client的nls_lang中的client字符集是US7ASCII。

下面是查看已经导出的/tmp/scott.dmp文件的字符集$ imp userid=scott/oracle file='/tmp/scott.dmp'show=yes

Import: Release 11.2.0.3.0 - Production onThu Aug 30 13:07:58 2012

Copyright (c) 1982, 2011, Oracle and/orits affiliates.Allrights reserved.

Connected to: OracleDatabase11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Withthe Partitioning, OLAP, Data MiningandRealApplication Testing options

Export file created byEXPORT:V11.02.00 via conventional path

import done inUS7ASCIIcharactersetandAL16UTF16NCHARcharacterset

import server uses ZHS16GBK characterset(possible charset conversion)

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

import done这行表示的client 的字符集是US7ASCII,导入导出端的字符集是相同的。

import server这行表示导入的数据库的字符集是ZHS16GBK,由于字符集不一样，所以会出现字符集的转换。

当exp client与imp client不一样的时候会出现下面的提示：$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

$ echo $NLS_LANG

AMERICAN_AMERICA.ZHS16GBK$ imp userid=scott/oracle file='/tmp/scott.dmp'show=yes

Import: Release 11.2.0.3.0 - Production onThu Aug 30 13:11:53 2012

Copyright (c) 1982, 2011, Oracle and/orits affiliates.Allrights reserved.

Connected to: OracleDatabase11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Withthe Partitioning, OLAP, Data MiningandRealApplication Testing options

Export file created byEXPORT:V11.02.00 via conventional path

import done inZHS16GBKcharactersetandAL16UTF16NCHARcharacterset

export client uses US7ASCII characterset(possible charset conversion)

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

export client uses US7ASCII character set (possible charset conversion)

export client这行表示导出client的字符集是US7ASCII。

更详细的见官方文档：NLS considerations in Import/Export - Frequently Asked Questions [ID 227332.1]