Expdp using NETWORK_LINK Corrupts the Special Characters or CLOBs (Doc ID 956877.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4
This problem can occur on any platform.

Symptoms

You started DataPump (expdp) over a network link to extract a table containing special characters from a remote database to a local dump. The two databases are using different character sets.

Then, after you imported tha export dump, the special character are damaged. In case the table contains CLOBs, the entire content of LOBs is corrupt.

This is demonstrated with the next test:

-- in a remote database with character set AL32UTF8
connect test/test

create table a_tab
(
   id    number,
   text  varchar2(10),
   c_lob clob
);

insert into a_tab values (1, 'Text', 'CLOB Text');
insert into a_tab values (2, 'Müller', 'CLOB Müller');
commit;

-- verify the content/codepoints
select text, dump (text, 16) "D_TEXT", c_lob, dump (dbms_lob.substr (c_lob, 100, 1), 16) "D_C_LOB" from a_tab;

TEXT          D_TEXT
------------- --------------------------------------------------
C_LOB         D_C_LOB
------------- --------------------------------------------------
Text 1        Typ=1 Len=6: 54,65,78,74,20,31
CLOB 1        Typ=1 Len=6: 43,4c,4f,42,20,31

Müller        Typ=1 Len=7: 4d,c3,bc,6c,6c,65,72
CLOB Müller   Typ=1 Len=12: 43,4c,4f,42,20,4d,c3,bc,6c,6c,65,72

-- in a local database with character set WE8ISO8859P1
-- create a database link to remote database with character set AL32UTF8
create database link o102al32 connect to test identified by test using 'o102al32';

-- start DataPump export using the created database link
#> expdp test/test directory=dpu dumpfile=a_tab.dmp tables=a_tab network_link=o102al32

This successfully ends:

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 22 October, 2009 12:56:51

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dpu dumpfile=a_tab.dmp network_link=o102al32 tables=a_tab
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."A_TAB"          5.914 KB          2 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/tmp/a_tab.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 12:57:03

-- start DataPump import using the created dumpfile
#> impdp test/test directory=dpu dumpfile=a_tab.dmp tables=a_tab

This successfully ends:

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 22 October, 2009 13:44:56

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=dpu dumpfile=a_tab.dmp tables=a_tab
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."A_TAB"          5.914 KB          2 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 13:44:59

Verify the results:

select text, dump (text, 16) "D_TEXT", c_lob, dump (dbms_lob.substr (c_lob, 100, 1), 16) "D_C_LOB" from a_tab;

TEXT          D_TEXT
------------- --------------------------------------------------
C_LOB         D_C_LOB
------------- --------------------------------------------------
Text 1        Typ=1 Len=6: 54,65,78,74,20,31
¿¿¿           Typ=1 Len=3: bf,bf,bf

M¿ller        Typ=1 Len=6: 4d,bf,6c,6c,65,72
¿¿¿¿¿         Typ=1 Len=5: bf,bf,bf,bf,bf

The special characters (and CLOBs) are corrupt.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms