Impdp and Network Link

A handy option of impdp command is the 'network link', which can be used to import one or many tables from a remote Oracle database to a local Oracle database.

Let's see how, shall we ?

• In the local database we create a 'DB_LINK'  that is linked to the database remote: CREATE DATABASE LINK DB_REMOTE CONNECT TO SCOTT IDENTIFIED BY 'XXX'    USING "alias_tnsnames";

• In the  remote database we grant the 'exp_full_database' privilege to the user owners of the DB_LINK created just before:     grant exp_full_database to scott;

Finally in the local database we execute the following command:

impdp userid=system tables=SCOTT.TABLE_NEW network_link=DB_REMOTE directory=DATA_PUMP_DIR logfile=TABLE_NEW.log remap_schema=OLD_SCHEMA:NEW_SCHEMA remap_tablespace=OLD_TBL:NEW_TBL

 Import: Release 11.2.0.1.0 - Production on Ven Feb 7 12:33:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Avvio di "SYS"."SYS_IMPORT_TABLE_01": 
Stima in corso con il metodo BLOCKS...
Elaborazione dell'object type TABLE_EXPORT/TABLE/TABLE_DATA
Stima totale con il metodo BLOCKS: 288 MB
Elaborazione dell'object type TABLE_EXPORT/TABLE/TABLE
. . importate "SCOTT"."TABLE_NEW" 1930168 righe
Job "SYS"."SYS_IMPORT_TABLE_01" completato in 12:33:33

Be careful : we have to specify the directory option in the 'impdp' command, because a log file will be written even if the table isn't downloaded in the filesystem directory.

Look out ! Who is writing doesn't know your system environment thereby I suggest to try in a Test environment before making mistakes in a production environment.

See you in my next blog !

Byeeeeeee