My Oracle Support Banner

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 11.1.0.6 to 11.1.0.6 [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: 11.1.0.6 to 11.1.0.6
This problem can occur on any platform.
Affects Impdp (Import Datapump) only.
Affected versions:
Database 10.1.0.2 to 10.1.0.5 and 10.2.0.1 to 10.2.0.3
Database 11.1.0.6
Fixed in 10.2.0.4 (and 10.2.0.5) and 11.1.0.7 patchset
Fixed in 11.2.0.1 and up
***Checked for relevance on 22-Jun-2012***
The original import tool (Imp) is not affected.

Symptoms

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.
An example:
In the original database the data looks like:

This is a test sentence that has here -> è wich is a non-US7ASCII character and here is the rest of the data in this row


After the impdp it may look like:

This is a test sentence that has here -> This is a test sentence that has here -> è wich is a non-US7ASCII character and here is the rest of the data in this row


or even missing a part of the data:

This is a test sentence that has here ->This is a test sentence that has here ->and here is the rest of the data in this row



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

Cause

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
Symptoms
Changes
Cause
Solution
 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:
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.