Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Oracle Database - Standard Edition - Version 8.0.3.0 and later
Information in this document applies to any platform.
PURPOSE
document characterset conversion when using Export/Import (both the old exp/imp and expdp/impdp).
DETAILS
For the main Globalization (NLS) FAQ please see: Note:60134.1 Globalization (NLS) - Frequently Asked Questions
For the main exp/imp FAQ please see: Note:175624.1 Oracle Server - Export and Import FAQ
1. How does NLS affect exp/imp (old import/export )?
Imp and exp 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 Old Exp/Imp (not datapump) and NLS Considerations Note:48644.1 Identifying the Export Character Set
2. How should NLS_LANG be set when using exp (old export ) ?
Oracle recommends to set the character set part of NLS_LANG environment parameter to ALWAYS the same character set as the character set of the database you are exporting. NLS_LANG=AMERICAN_AMERICA.
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 original 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 imp.
Note that:
this has no relation with the Operating system. If your source database is a WE8MSWIN1252 database then you simply should set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before import, even on a Unix server.
during *interaction* with the database (for example sqlplus) you need to configure your (Unix) client properly and that may be different from the NLS_CHARACTERSET.
3. How should NLS_LANG be set when using imp (old 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 exp and the imp side.
NLS_LANG=AMERICAN_AMERICA.
Even if the character sets of the exporting and importing databases are not the same the best (preferred) value to use for the character set part of NLS_LANG on both exp and imp is still the character set of the source database.
NLS_LANG=AMERICAN_AMERICA.
Setting the NLS_LANG to the character set of the target database during imp is also correct as such, but import has some limitations when going to a multibyte characterset (like UTF8), hence using the SOURCE NLS_CHARACTERSET during both imp and exp session is simply the best option, avoiding any problems like IMP-16 "Required character set conversion (type %lu to %lu) not supported".
So, the preferred place to do the conversion is between the import executable and the target database.
Note that:
this has no relation with the Operating system. If your source database is a WE8MSWIN1252 database then you simply should set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before import, even on a Unix server.
during *interaction* with the database (for example sqlplus) you need to configure your (Unix) client properly and that may be different from the NLS_CHARACTERSET.
3.a) double check the NLS_CHARACTERSET on the SOURCE database
An example: you want to go from an WE8MSWIN1252 to an AL32UTF8 db using the old exp/imp tool:
Note that this is only the exp/imp example, if you want to migrate to AL32UTF8 or UTF8 check Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g and /or Note 1297961.1 ORA-01401 / ORA-12899 While Importing Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database .
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
export with the NLS_LANG set to AMERICAN_AMERICA.
(This is also the setting you want to use if you take an export using exp as backup)
In this case we want to create a export file containing WE8MSWIN1252 data.
on unix this is:
$ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
$ export NLS_LANG
$ exp ....
The conversion to UTF8 is done while inserting the data in the UTF8 database by the imp connection.
We recommend to set the NLS_LANG explicit in the current shell for unix (->How to Set Unix Environment Variable ) or in the dos box used for the exp or imp tool on windows (-> "c:\>set NLS_LANG=AMERICAN_AMERICA.")
4. How are exp and imp affected by the NLS_LANGUAGE and NLS_TERRITORY ?
Not. Normally you use the AMERICAN_AMERICA default, but if you imported with NLS_LANG set to FRENCH_FRANCE for example then you will not have problems, even if the originating environment used GERMAN_GERMANY or so.
There is no relation between NLS_LANGUAGE and NLS_TERRITORY and the actual languages stored in the database.
5. I have the message "( possible ncharset conversion )" during imp.
You see something similar to
Export file created by EXPORT:V08.01.07 via direct path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
export server uses WE8ISO8859P15 NCHAR character set (possible ncharset conversion)
in the import log , this is normal and is not a error condition.
- If you do not use N-types for USER/application data then this is a pure informative message.
This select will give all N-type tables:
select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB');
- But even in the case that you use N-types like NCHAR or NCLOB then this is not a problem:
* the database will convert from the "old" NCHAR characterset to the new one automatically. (and - unlike the "normal" characterset - the NLS_LANG has no impact on this conversion during exp/imp)
* AL16UTF16 or UTF8 (the only 2 possible values in 9i) are unicode characterset and so can store any character... So no data loss is to be expected. SeeThe National Character Set in Oracle 9i, 10g and 11g
6. How to know in what characterset a dmp (old exp) file is created?
simply issue: imp system/oracle@database show=yes file=test.dmp
the output gives you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
-> this is the current NLS_LANG value set in the environment
and the NCHAR characterset of the target database
import server uses WE8MSWIN1252 character set (possible charset conversion)
-> this is only shown if the NLS_LANG during this import session is different
from the target database characterset, so if you see 3 lines you might have problems :-)
export client uses UTF8 character set (possible charset conversion)
-> this is the characterset used during the export session and the
characterset used in the dmp file.
7. How does NLS_LANG affect DataPump (expdp/impdp)?
Datapump does not use the NLS_LANG to do conversion between databases.
Conversion between 2 database charactersets is done purely based on the NLS_CHARACTERSET ( or NLS_NCHAR_CHARACTERSET for Nchar,Nvarchar and Nclob datatypes) of the source and target database.
However, if you specify a parameter file then the NLS_LANG *is* used for the encoding of the parameter file. This is only important if you use non-English characters (e.g. for the QUERY parameter) in the parameter file.
If you use non-English characters in the parameter file then the NLS_LANG environment variable should be set (in the session where the Data Pump job is started) to the correct encoding of the parameter file (!).
Do NOT use Expdp/Impdp when going to (AL32)UTF8 or an other multibyte characterset on ALL 10g versions lower then 10.2.0.4 (including 10.1.0.5). Also 11.1.0.6 is affected.
It will provoke data corruption unless is applied on the Impdp side. Expdp is not affected, hence the data in the dump file is correct.Also the "old" exp/imp tools are not affected.
This problem is fixed in the 10.2.0.4 and 11.1.0.7 patch set.
Fixed in 11.2.0.1 and up
For windows the fix is included in
10.1.0.5.0 Patch 20 (10.1.0.5.20P) or later, see Note 276548.1 .
10.2.0.3.0 Patch 11 (10.2.0.3.11P) or later, see Note 342443.1 .
8. What causes ORA-01401 or ORA-12899 during import (imp and impdp) ?
9i and lower gives ORA-01401: inserted value too large for column , 10g and up gives ORA-12899: value too large for column
This may be seen when exporting from a database with a 8 bit NLS_CHARACTERSET like WE8ISO8859P1, WE8MSWIN1252 , WE8DEC ...) to a database with a 16 bit NLS_CHARACTERSET (like JA16SJIS , ZHS16GBK, KO16MSWIN949) or NLS_CHARACTERSET set to AL32UTF8 or UTF8
Please see Note 1297961.1 ORA-01401 / ORA-12899 While Importing Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database.
