Datapump Network Link Option

One of the nicest features that came out in Oracle 10g was datapump. One of the least publicized but nicest capabilities of datapump is the ability to transfer data directly from one instance to another without using a datafile to transport the data. The data is transported across a db link from one instance to another. Datapump still requires a directory on the server to store some information, but the data itself is transferred directly. This is a huge time and space saver for those large data transfer operations. This is how it is done:

SQL> create directory dpump_dir as ‘/tmp’;
SQL> create public database link source_db using ‘source_db’;
SQL> exit
impdp system/system directory=dpump_dir network_link=source_db schemas=scott remap_schema=scott:dest_user;

The command above will import the scott schema from source_db to the current database, and rename the schema to dest_user. If the dest_user does not exist, it will be created.

7 Responses to “Datapump Network Link Option”

  1. Sundar Says:

    Where should I create the dpump_dir? I assume in the source server. I tried that. But I still get error that the directory is invalid.

    • dbakerber Says:

      dpump_dir is directory created on the source server, but it must also be created within oracle with the oracle ‘create directory’ command, for example:

      create directory dpump_dir as ‘/home/oracle’

      make sure that the Oracle user has write permission on the directory.

  2. Sundar Says:

    Thanks. This is what I did. The key point is the data pump directory should be created in the target database and the path should be poiting to source server’s directory location.

    1. Login as system and created a user called ORA_SUNDAR in the target server

    2. Login as system to target server and recreate the directory data_pump_dir pointing to source server’s data_pump_dir location. Make sure to “Map Network Drive” to \\xxx\xxxx. Grant read, write permission on directory data_pump_dir to ORA_SUNDAR.

    SQL> drop directory data_pump_dir;

    Directory dropped.

    SQL> create directory data_pump_dir as ‘ \\xxx\xxxx\’;
    Directory created.

    SQL> grant write,read on directory data_pump_dir to ORA_SUNDAR;

    Grant succeeded.

    3. Create a public linked server called “linka” and connect to source schema.
    CREATE PUBLIC DATABASE LINK “LINKA” CONNECT TO ora_sundar IDENTIFIED BY ora_sundar USING ‘net service name’;

    4. Open a DOS window in the target server and change the directory to the oracle bin directory.

    C:\Documents and Settings\Oracle>cd C:\oracle\product\11.1.0\db_1\BIN

    5. Run the data pump import. Example shows moving only one table called “client” from source to target. If you remove the tables parameter, the whole schema will be refreshed with objects from source schema.

    impdp system/pwd tables=client network_link=LINKA directory=data_pump_dir remap_schema=ora_sundar:ora_sundar

    • dbakerber Says:

      Not quite, and I should have made this more clear. The directory must be defined on the database in which you are running datapump, the source or destination is irrelevant, it just mus exist in the database you are running in.

  3. Sundar Says:

    You are right. I created the directory pointing to “C:\” and I was able to do data pump import. Thanks

  4. Forrest Says:

    I blog often and I really appreciate your content.

    This article has really peaked my interest. I will take a note of your site and keep checking for new details
    about once per week. I opted in for your Feed too.

    Forrest

  5. pete Says:

    Radical attempt bro. Like your effort.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: