NETWORK LINK in datapump

NETWORK LINK: in 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:

Here is the DEMO:

We have two databases on two different servers:

Source database: delhi---->server1

Target database: noida----->server2

Connect to database "delhi" on server1:

c:\>sqlplus sys/sys@delhi as sysdba

SQL> create user abc identified by abc;

User created.

SQL> grant CREATE PUBLIC DATABASE LINK,CREATE SESSION to abc;

Grant succeeded.

SQL> grant CREATE TABLE,insert any table to abc;

Grant succeeded.

SQL> grant create session to abc;

Grant succeeded.

SQL> conn abc/abc

Connected.

SQL> create table p

  2  (id number);

Table created.

SQL> insert into p values(3);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p;

        ID

----------

         3

         3

         3

         3

         3

Now,connect to database "noida" on server2:

SQL>create directory gpump as 'D:\network\';

SQL>grant read,wrie on directory gpump to system;

SQL> create public database link h_link connect to abc identified by abc using 'delhi';

Database link created.

Checking database link:

SQL> select * from p@h_link;

        ID

----------

         3

         3

         3

         3

         3

Now start the import operation that will import schema "abc" from database "delhi" to database "noida" without actually creating any dumpfile;

c:\>impdp system/sys@noida LOGFILE=import.schema.log NETWORK_LINK=h_link SCHEMAS=abc directory=gpump

Import: Release 11.2.0.1.0 - Production on Thu Sep 22 16:10:47 2011

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 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@noida LOGFILE=import.schema.log NETWORK_LINK=h_link SCHEMAS=abc directory=gpump

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "ABC"."P"                                        5 rows

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:10:47

SQL> conn abc/abc@noida

Connected.

SQL> selct * from tab;

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

P                              TABLE

SQL> select * from p;

        ID

----------

         3

         3

         3

         3

         3

Now, we will export the schema "abc" present in database "delhi:server1" to "noida:server2" but before that drop the user "abc" from database "noida" to avoid ambiguity:


C:\>sqlplus sys/sys@noida as sysdba 



SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 22 16:51:08 2011


Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




SQL> drop user abc cascade;


User dropped.

SQL> host expdp system/ramtech@noida LOGFILE=export_schema.log dumpfile=ff.dmp NETWORK_LINK=h_link SCHEMAS=abc directory=gpump

Export: Release 11.2.0.1.0 - Production on Thu Sep 22 16:52:42 2011

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 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/********@noida LOGFILE=export_schema.log dumpfile=ff.dmp NETWORK_LINK=h_link SCHEMAS=abc directory=gpump

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . exported "ABC"."P"                                   5.039 KB       5 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:

  D:\NETWORK\FF.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:52:46

You will see the export dumpfile will create on server1 from server2.

Enjoy:-)