Oracle view the character set in the imp exported file

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].