1. How does NLS affect import/export?
Import and export are client products, in the same way as SQL*Plus or
Oracle Forms, and will therefore translate characters from the database
character set to that defined by NLS_LANG. The character set used for
the export will be stored in the export file and, when the file is imported,
the import will check the character set that was used. If it is different to
that defined by NLS_LANG at the import site, the characters will be
translated to the import character set and then, if necessary, to the
database character set.
References:
[NOTE:15095.1] Export/Import and NLS Considerations
[NOTE:48644.1] Identifying the Export Character Set
2. How should NLS_LANG be set when using export?
It's highly recommended to set to character set part of NLS_LANG to the
same character set as the character set of the database you are exporting.
select value from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
That way no conversion will take place and the exportfile will be created
in the same character set as the orginal database and contain ALL data from
original database (even incorrectly stored data if that would be the case).
Even if the plan is to import this into a database with a different character set
later the conversion can be postponed until the import.
3. How should NLS_LANG be set when using import?
If the source and target database have the same character set,
the character set part of the NLS_LANG should be set to that same character set
on both the export and the import.
Even if the character sets of the exporting and importing databases
are not the same the best (prefered) value to use for
the character set part of NLS_LANG on both export and import is still
the character set of the source database.
But setting the NLS_LANG to the character set of the target database during import
is also correct.
That way conversion only takes place once, either on export or on import.
However, the preferred place to do the conversion is between the import executable
and the target database.
Comments