How to Self-Import by Data Pump in Network Mode

  • by
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 *