DataPump Expdp Or Impdp With NETWORK_LINK Returns No Rows Exported Or Imported For Partitioned Table (Doc ID 1381419.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2013***

Symptoms

On version 11.2.0.1, DataPump export or import (expdp or impdp) via a NETWORK_LINK returns no rows exported or imported for partitioned table, like the next test case demonstrates:

connect tc/tc

create table test_tab
(
   deptno          number,
   deptname        varchar2(20),
   quarterly_sales number(10, 2),
   state           varchar2(2)
)
partition by list (state)
(
   partition northwest    values ('OR', 'WA'),
   partition southwest    values ('AZ', 'UT', 'NM'),
   partition northeast    values ('NY', 'VM', 'NJ'),
   partition southeast    values ('FL', 'GA'),
   partition northcentral values ('SD', 'WI'),
   partition southcentral values ('OK', 'TX')
);

-- populate the table
insert into test_tab values (11, 'support',   111, 'WA');
insert into test_tab values (12, 'support',   122, 'WA');
insert into test_tab values (20, 'technical', 200, 'NM');
insert into test_tab values (30, 'CEO',       300, 'NJ');
insert into test_tab values (40, 'fin',       400, 'GA');
insert into test_tab values (50, 'manfac',    500, 'WI');
insert into test_tab values (10, 'HR',        10,  'TX');
commit;

-- The output of statement:

select SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
from   dba_objects
where  object_name = 'TEST_TAB';

shows OBJECT_ID = DATA_OBJECT_ID.

-- Truncate the table in order to generate new data_object_id's
-- for the partitioned table, re-run the insert statements and commit;

select SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
from   dba_objects
where  object_name = 'TEST_TAB';

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------- --------- -------------- ---------------
SOUTHCENTRAL      138552         138558 TABLE PARTITION
NORTHCENTRAL      138551         138557 TABLE PARTITION
SOUTHEAST         138550         138556 TABLE PARTITION
NORTHEAST         138549         138555 TABLE PARTITION
SOUTHWEST         138548         138554 TABLE PARTITION
NORTHWEST         138547         138553 TABLE PARTITION
TEST_TAB          138546                TABLE

Now OBJECT_ID != DATA_OBJECT_ID

-- If DataPump export through network_link now runs,
-- then the rows are not exported:

#> expdp tc/tc network_link=dblink1 tables=test_tab dumpfile=tc_test2.dmp

 

Export: Release 11.2.0.1.0 - Production on Thu May 12 16:33:44 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TC"."SYS_EXPORT_TABLE_01": tc/******** network_link=dblink1 tables=test_tab dumpfile=tc_test2.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TC"."TEST_TAB":"NORTHCENTRAL" 6.257 KB 0 rows
. . exported "TC"."TEST_TAB":"NORTHEAST"    6.257 KB 0 rows
. . exported "TC"."TEST_TAB":"NORTHWEST"    6.257 KB 0 rows
. . exported "TC"."TEST_TAB":"SOUTHCENTRAL" 6.257 KB 0 rows
. . exported "TC"."TEST_TAB":"SOUTHEAST"    6.257 KB 0 rows
. . exported "TC"."TEST_TAB":"SOUTHWEST"    6.257 KB 0 rows
Master table "TC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TC.SYS_EXPORT_TABLE_01 is:
/oracle/admin/d1v11201/dpdump/tc_test2.dmp
Job "TC"."SYS_EXPORT_TABLE_01" successfully completed at 16:33:58

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