Why Is Metadata DataPump Export Faster When Started Over A Network Link Than Locally? (Doc ID 1357839.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2 - Release: 10.2 to 11.2
Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2   [Release: 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

You started DataPump export (expdp) locally (on database server) to export the metadata of a certain schema containing partitioned/subpartitioned tables.

Later you started the same export but from a remote target machine/database (with same version) using a network link connected to the source server/database. This considerably needed fewer time to complete than the local export.

Let's follow the next test that demonstrates this:

-- source database
connect / as sysdba

create or replace directory tmp as '/tmp';

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;

connect test/test

-- create 200 partitioned tables each
-- with 20 partitions and 2 subpartitions
declare
  stmt varchar2(32000);

begin
  for i in 1..200 loop
    stmt := 'create table parttab'||lpad (to_char (i), 3, '0')||
            ' (col001 number, col002 varchar2(100)) '||
            'partition by range (col001) subpartition by hash '||
            '(col002) subpartitions 2 (';
    for j in 1..20 loop
      stmt := stmt||'partition p'||lpad (to_char (j), 3, '0')||
             ' values less than ('||to_char ((j * 1000) + 1)||')';
      if j < 20 then
        stmt := stmt||', ';
      end if;
    end loop;
    stmt := stmt||')';
    execute immediate stmt;
  end loop;
end;
/

Then export the user locally:

#> expdp system/password directory=tmp dumpfile=test_local.dmp schemas=test logfile=test_local.log content=metadata_only reuse_dumpfiles=y metrics=y include=table

This shows:

Export: Release 11.2.0.2.0 - Production on Tue Sep 13 12:31:32 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=tmp dumpfile=test_local.dmp schemas=test logfile=test_local.log content=metadata_only reuse_dumpfiles=y metrics=y include=table
Processing object type SCHEMA_EXPORT/TABLE/TABLE
   Completed 200 TABLE objects in 102 seconds
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
   /TMP/TEST_LOCAL.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:36

=> 102 seconds were needed.

Then export the same from target/remote database:

#> expdp system/password network_link=o112rem directory=tmp dumpfile=test
_remote.dmp schemas=test logfile=test_remote.log content=metadata_only reuse_dumpfiles=y metrics=y include=table

This shows:

Export: Release 11.2.0.2.0 - Production on Tue Sep 13 12:34:46 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_04": system/******** network_link=o112rem directory=tmp dumpfile=test_remote.dmp schemas=test logfile=test_remote.log content=metadata_only reuse_dumpfiles=y metrics=y include=table
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 200 TABLE objects in 15 seconds
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
   /TMP/TEST_REMOTE.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully completed at 12:35:14

=> only 15 seconds were needed.

A similar DataPump import (impdp) performed on target using the same network link:

#> impdp system/password network_link=o112rem directory=tmp schemas=test logfile=test_remote.log content=metadata_only metrics=y

shows:

Import: Release 11.2.0.2.0 - Production on Tue Sep 13 12:37:19 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/******** network_link=o112rem directory=tmp schemas=test logfile=test_remote.log content=metadata_only metrics=y
Processing object type SCHEMA_EXPORT/USER
   Completed 1 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
   Completed 1 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
   Completed 2 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
   Completed 1 DEFAULT_ROLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
   Completed 1 PROCACT_SCHEMA objects in 7 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
   Completed 200 TABLE objects in 52 seconds
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully completed at 12:38:24

=> only 52 seconds were needed to extract the tables from remote schema and create them into target schema.

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