EXPDP / IMPDP corrupts the data for a LONG column

Few months back, we were doing OS migration for database servers.

From: HP-UX 11.11 (PA RISC), Oracle 10gR2 (10.2.0.4) RAC

To: HP-UX 11.23 (ia64), Oracle 10gR2 (10.2.0.4) RAC with PSU2

The data migration approach was EXPDB and IMPDP with data pump and we encounter following bug (described in metalink notes).

Bug 5598333: EXPDP/IMPDP corrupts the data for a LONG column

Affected version (confirmed):

10.2.0.3 & 10.2.0.4

Fixed in version:

10.2.0.5 (patchset)

11.1.0.6 (Base Release)

patch 5598333 available for 10gR2

Note:  This patch or fix should be applied before issue occurred to prevent mentioned bug. In another word, this patch or fix does not resolve this issue if data corruption happen before patch applied.

Symptom Description:

Logical data corruption can occur after a Data Pump import under the following conditions:

1. the character set of the export database is a multibyte character set such as AL32UTF8
2. the table contains a column of LONG datatype
3. the table has had columns added after it was created using the SQL
“alter table” statement such that the LONG column is not the last in
the table.
For example,
At source:
SQL> create table test (col1 number, col2 long not null);
SQL> alter table test add (col3 number not null);
SQL> insert into test values (1,’test-test’,1);
SQL> commit;
$ expdp test1/test1 directory=dpump_dir tables=test dumpfile=test_table.dmp
At destination:
$ impdp  test1/test1 directory=dpump_dir dumpfile=test_table.dmp tables=test
SQL> select col2 from test;
>> “t e s t – t e s t”
=> Notice extra blank spaces are inserted after each character for the long column
Posted by helloworld in Database General, Oracle Database
No Comments Read more

Tagged Under : , , , , ,

Make a Comment