Using Data Pump Export(expdp) Over Network

Data Pump Export can be used over Networks. Normally when data pump export utility(expdp) executes, it creates a dump file to the server where the database is installed. You can DIRECTORY parameters in order to specify operating system file path.
Suppose that you have 2 databases(srv1db and srv2db) in 2 different servers(srv1 and srv2). If you run expdp on srv2db, dump file shall be created in srv2 server. You can also run expdp on srv1db and dump file shall be created in srv2 server, with NETWORK_LINK expdp parameter. See demostration below:


--srv1db
SQL> select DIRECTORY_PATH from all_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/usr/users/oracle/DP

SQL>


--srv2db
SQL> select DIRECTORY_PATH from all_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/home/oracle/DP

SQL>

---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr


--srv2
bash-3.00$ ls -ltr /home/oracle/DP




--expdp on srv2
bash-3.00$ expdp mte/mte@srv1db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:15:53

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MTE"."SYS_EXPORT_TABLE_01": mte/********@srv1db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log
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 "MTE"."T" 5.125 KB 31 rows
Master table "MTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MTE.SYS_EXPORT_TABLE_01 is:
/usr/users/oracle/DP/mte_t.dmp
Job "MTE"."SYS_EXPORT_TABLE_01" successfully completed at 10:12:11



---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr
total 2455
-rw-r----- 1 oracle dba 69632 Aug 13 10:12 mte_t.dmp
-rw-r--r-- 1 oracle dba 981 Aug 13 10:12 mte_t.log
uccs01 oracle@srv1:/usr/users/oracle/DP>


--srv2
bash-3.00$ ls -ltr /home/oracle/DP




--srv2db, create a dblink for srv1db
SQL>create public database link srv1db_LINK.ORC
connect to USER1
using 'srv1db.ORC';


--expdp on srv2
bash-3.00$ expdp USER1/USER1@srv2db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t2.dmp logfile=mte_t2.log network_link=srv1db_link

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:22:09

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "USER1"."SYS_EXPORT_TABLE_01": USER1/********@srv2db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t2.dmp logfile=mte_t2.log network_link=srv1db_link
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER1"."T" 5.351 KB 16 rows
Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER1.SYS_EXPORT_TABLE_01 is:
/home/oracle/DP/mte_t2.dmp
Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 10:22:45

bash-3.00$



---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr
total 2455
-rw-r----- 1 oracle dba 69632 Aug 13 10:12 mte_t.dmp
-rw-r--r-- 1 oracle dba 981 Aug 13 10:12 mte_t.log
uccs01 oracle@srv1:/usr/users/oracle/DP>


--srv2
bash-3.00$ ls -ltr /home/oracle/DP
-rw-r--r-- 1 oracle oinstall 0 Aug 13 10:22 mte_t2.log
-rw-r----- 1 oracle oinstall 4096 Aug 13 10:22 mte_t2.dmp
bash-3.00$