Possible datacorruption with Impdp if importing NLS_CHARACTERSET is different from exporting NLS_CHARACTERSET
(Doc ID 457526.1)
Last updated on FEBRUARY 19, 2019
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.3 [Release 10.1 to 10.2]
Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 11.1]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 184.108.40.206 to 220.127.116.11
This problem can occur on any platform.
Affects Impdp (Import Datapump) only.
Database 10.1.0.2 to 10.1.0.5 and 10.2.0.1 to 10.2.0.3
Fixed in 10.2.0.4 (and 10.2.0.5) and 18.104.22.168 patchset
Fixed in 22.214.171.124 and up
***Checked for relevance on 22-Jun-2012***
The original import tool (Imp) is not affected.
When the target (impdp side) NLS_CHARACTERSET is different from the source (expdp side) NLS_CHARACTERSET then Impdp (Import Datapump) may provoke random silent corruption of some CHAR, VARCHAR2, CLOB and LONG data.
Please note that:
* The original imp tool is not affected by this problem.
* Impdp will not give errors or warnings, the corruption can only been seen on some rows when selecting the imported dataset.
* Not all data is corrupted, only a certain part. Typical figures are around 50 rows on 500.000 rows for 8 bit to multibyte (AL32UTF8), but this depends also on your dataset so it can be more or less. When going from 8bit to 8 bit the possibility to have a corruption is very low.
* This is a impdp problem, the expdp dmp files are not corrupted.
* It only happens during characterset conversions, EBCDIC characterset are not affected.
This bug may provoke two similar kinds of silent corruption:
A) Data may be corrupted around the first non-US7ASCII character but selecting will NOT provoke a error because the corrupted data is not longer then the defined column length.
Typically this will provoke corruption that repeats a part of the row's contents.
In the original database the data looks like:
After the impdp it may look like:
or even missing a part of the data:
B)In addition to the corrupted data some of the corrupted rows may contain more data then the column length alows, for example if you do a
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|If you are planning to use impdp for a characterset change:|
|If you already have used expdp/impdp for a characterset migration and you are affected:|