Data Pump

How to Self-Import by Data Pump in Network Mode

If you would like to copy tables from one schema to another, the first idea could be Create Table As Select (CTAS), but CTAS is a long running operation especially the table is quite large.

Here I introduce a way to copy tables using data pump with network mode in 11g. And I assume that you have read my another post about network mode of data pump:
How to Use Data Pump Network Mode – Step by Step
  1. Create a database link to point to the database itself.
  2. [oracle@primary01 ~]$ sqlplus system/password
    ...
    SQL> create database link primdb_self connect to system identified by password using 'primdb1';

    Database link created.

  3. Import the tables with remap_schema.
  4. [oracle@primary01 ~]$ impdp system/password remap_schema=HR:SH tables=HR.EMPLOYEES exclude=ref_constraint,trigger network_link=primdb_self

    Import: Release 11.2.0.1.0 - Production on Tue Jul 2 20:44:44 2013

    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, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** remap_schema=HR:SH tables=HR.EMPLOYEES exclude=ref_constraint,trigger network_link=primdb_self
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    . . imported "SH"."EMPLOYEES"                               107 rows
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/COMMENT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 20:45:10
  5. Check the result:
  6. SQL> select count(*) from sh.employees;

      COUNT(*)
    ----------
           107
If your data pump jobs take a very long time to complete, you may refer to my post about speeding up the jobs:
5 Tips to Speed Up Data Pump Export or Import

Leave a Reply

Your email address will not be published. Required fields are marked *