EXPORT/IMPORT AND NLS CONSIDERATIONS

If you have exported/imported a database or table(s) and are now encountering
character set conversion problems, use the following information to
confirm whether the export/import procedure was performed correctly:

-- Establish the character set the export database was exported under
by reading the first few bytes of the export file. Export stores the
character set ID (not the text string) of the character set defined by
NLS_LANG in the export dump file. See [NOTE:48644.1].
-- Establish NLS_LANG environment variable for Import session.
-- Import reads the Export character set ID from the dump file and
compares it with the session's character set as defined in NLS_LANG.
-- No conversion occurs if the Export character set and the Import
session character set are the same. If they are not the same,
conversion is performed from the Export character set to the Import
session character set prior to the data being inserted into the
database. However, import can only perform this conversion for
single-byte character sets.
-- For imports into multibyte character set databases (i.e. UTF8) the
character set part of NLS_LANG should be set identical to that of the
export session. Otherwise an
IMP-16 "Required character set conversion (type %lu to %lu) not
supported" error will come up during import. See also [BUG:896407].
-- The Import session character set should be a superset of the Export
character set otherwise special characters will not be correctly
converted.
-- Include the parameter 'CHARSET' when defining the Import parameter set.
CHARSET identifies the character set of the Export file. Currently
in V7, the code expects the value in CHARSET to match the Export's
file character set. If they do not match, IMP-42 will result.
The CHARSET option was developed to import older export files
which did not have stored character set ID information.
-- After the data has been converted to the Import session character
set, it is then converted to the database character set if they
differ. The database character set should be a superset of the
Import's session character set otherwise special characters will not
be correctly converted.

-------------------
| db in character | export exp session is in character
| set A |---------------------> set B as defined by NLS_LANG.
------------------- Therefore the dump file is in
source character set B. Character set
conversion may occur.

|
|
| move file over to
| another machine
|
V
destination
-------------------
| db in character | import imp session is in character
| set D | <------------------- set C as defined by NLS_LANG.
------------------- The dump file is still in
character set B. Character set
conversion may occur.
During the import process
character set conversion
may occur between character
set C and the db's character
set D if they differ.

You need to identify the following:
-- What was the database character set specified when issuing the
CREATE DATABASE for the source database (character set A in the above)?
-- What was the client character set when the data was exported (character
set B in the above)?
-- What was the database character set specified when issuing the CREATE
DATABASE for the destination database (character set D in the above)?
-- What was the client character set when the data was imported?


========================================================================================================

HTHU

Ankur Shah
Oracle DBA
DHR-GA
----- Original Message -----
From: Nitin Gupta
To: LazyDBA.com Discussion
Sent: Wednesday, July 31, 2002 2:28 AM
Subject: Export/Import-Characterset difference


I want to do EXPORT from one database and IMPORT to different database, both having different CHARACTERSETS, on Oracle 8i.
How to go about it, 'error-free'? Kindly suggest some detailed document also if any, relecting various options/implications.

Best regards,
Nitin Gupta
Oracle LazyDBA home page