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
DBA 