我們在exp導入的時候，經常會有警告字符不一致的問題。下面來看看在imp/exp導入導出的時候，nls_lang這個參數到底應該怎么來配置。 oracle推薦在執行exp的時候nls_lang配置與database nls_character一致，這樣不會出現字符轉換的問題，能提高exp的效率。查看d
我們在exp導入的時候，經常會有警告字符不一致的問題。下面來看看在imp/exp導入導出的時候，nls_lang這個參數到底應該怎么來配置。
Oracle推薦在執行exp的時候nls_lang配置與database nls_character一致，這樣不會出現字符轉換的問題，能提高exp的效率。查看database nls_character
SQL> select value from nls_database_parameters where parameter=’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 on Thu Aug 30 13:07:22 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 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?
About to export SCOTT’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT’s tables via Conventional Path …
. . exporting table????????????????????????? BONUS????????? 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table?????????????????????????? DEPT????????? 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table??????????????????????????? EMP???????? 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table?????????????????????? SALGRADE????????? 5 rows exported
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 and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
?? 從上面提示可以看到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 on Thu Aug 30 13:07:58 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (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 on Thu Aug 30 13:11:53 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (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]
