Datapump network_link export and import

This post show you, just a easy understanding about Datapump network_link parameter.

I have used two site database

Broker and ACE

Step 1:

Source Database

Go to Broker database,

SQL> create user nettest identified by nettest;

User created.

SQL> grant connect,resource,dba to nettest;

Grant succeeded.

SQL> conn nettest/nettest@broker.world;
Connected.
SQL> create table mynet(empno number,empname varchar2(20));

Table created.

SQL> insert into mynet values(001,’azar’);

1 row created.

SQL> insert into mynet values(002,’jahir’);

1 row created.

SQL> commit;

Commit complete.

Step 2:

Target Database

Go to ACE database

SQL> conn / as sysdba
Connected.
SQL> create user mynet identified by mynet;

User created.

SQL> grant connect,resource,dba to mynet;

Grant succeeded.

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

Grant succeeded.

SQL> commit;

Commit complete.

SQL> conn mynet/mynet;
Connected.

SQL> select * from tab;

no rows selected

SQL> create database link useme connect to nettest identified by nettest using ‘
BROKER.world’;

Database link created.

SQL> commit;

Commit complete.

SQL> select * from dba_directories where directory_name like ‘DATA_PUMP_DIR%’;

OWNER                          DIRECTORY_NAME
—————————— ——————————
DIRECTORY_PATH
——————————————————————————–

SYS                            DATA_PUMP_DIR
c:\oracle\product\10.2.0\admin\ace\dpdump\
SQL> select * from tab;

no rows selected

SQL>

 

SQL> select * from dba_db_links where owner=’MYNET’;

OWNER
——————————
DB_LINK
—————————————————-

USERNAME
——————————
HOST
—————————————————-

CREATED
———
MYNET
USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM
NETTEST
BROKER.world
27-OCT-10

Step 3:

Go to target server (ace db server) and execute your export command here, you can see your broker DB schema (nettest) dump file stored in ACE db server schema directory (mynet).

C:\Documents and Settings\Administrator>expdp mynet/mynet@ace dire
ctory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=t
estnet.log dumpfile=testnet.dmp tables=mynet

Export: Release 10.2.0.1.0 – 64bit Production on Wednesday, 27 October, 2010 16:
53:52

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_EXPORT_TABLE_01″:  mynet/********@ace directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnet.log
dumpfile=testnet.dmp tables=mynet
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
. . exported “NETTEST”.”MYNET”                            5.25 KB       2 rows
Master table “MYNET”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYNET.SYS_EXPORT_TABLE_01 is:
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ACE\DPDUMP\TESTNET.DMP
Job “MYNET”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:55:02

Export Finished and then If you need , you can import the dump file from broker db server to ace db server.

For import, dumpfile parameter not need.
C:\Documents and Settings\Administrator>impdp mynet/mynet@ace dire
ctory=data_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=t
estnetimp.log remap_schema=nettest:mynet  tables=mynet

Import: Release 10.2.0.1.0 – 64bit Production on Wednesday, 27 October, 2010 17:
00:45

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting “MYNET”.”SYS_IMPORT_TABLE_01″:  mynet/********@ace directory=data
_pump_dir network_link=USEME.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=testnetimp.
log remap_schema=nettest:mynet tables=mynet
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 “MYNET”.”MYNET”                                  2 rows
Job “MYNET”.”SYS_IMPORT_TABLE_01″ successfully completed at 17:02:15
C:\Documents and Settings\Administrator.ACE-INS>

 Step 4:

SQL> conn mynet/mynet@ace
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
MYNET                          TABLE

SQL> select * from mynet;

     EMPNO EMPNAME
———- ——————–
         1 azar
         2 jahir

SQL>

 

If you need to refer about network_link parameter, you can from here

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1011008