NLS_LANG during Export/Import

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.


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

8 views0 comments

Recent Posts

See All

Get DDL for Oracle object

set feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set serveroutput on format wrapped set def off set tab off exec dbms_metad

Cancelled scheduled concurrent requests

This SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for identifying scheduled requests that failed to be resubmitted. select

Foreign key constraint violation

/* This PL/SQL script checks violations of foreign key constraints Example of the tables: CREATE TABLE temp_supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_na