During exp import, we often encounter the issue of inconsistent warning characters. The following describes how to configure the nls_lang parameter during imp/exp import and export.
It is recommended that the configuration of nls_lang be consistent with that of database nls_character when executing exp, so as to avoid character conversion problems and improve exp efficiency. View database nls_character
- SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
-
- VALUE
- ------------------------------
- ZHS16GBK
Here we only check the character set of the exported exp file.
- $ 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.
The above prompt shows that the database character set is ZHS16GBK, while the client character set in my client's nls_lang is US7ASCII.
The following shows the character set of the exported/tmp/scott. dmp file.
- $ 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)
In the import done line, the client character set is US7ASCII, and the character set at the import and export end is the same.
The import server line indicates that the character set of the imported database is ZHS16GBK. Because the character set is different, Character Set conversion may occur.
The following prompt appears when the exp client is different from the 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)
The export client line indicates that the character set of the exported client is US7ASCII.
For more details, see the official documentation NLS considerations in Import/Export-Frequently Asked Questions [ID 227332.1].