My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

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
  Cause
  Solution
  References

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