Question: How does the network_link syntax
of data pump allow me to export and import between databases?
expdp and impdp is only one of
many options to migrate tables between databases.
network_link option is great because you no longer need to
have cumbersome dmp files on the server, and with
network_link, the data can be transferred directly across
the network, bypassing any need for a dump file.
direct export/imports between databases.
use network_link you follow these simple steps:
1 - Create a TNS entry for
the foreign database in your tnsnames.ora file
2 - Test with tnsping
3 - Create a database link to
the foreign database
4 - Specify the database link
as network_link in your expdp or impdp syntax
Using the network_link syntax, you
can initiate an export job from your server and have Data
Pump export data from a remote database to dump files
located on the instance from which you initiate the Data
Pump export job.
expdp hr/hr DIRECTORY=dpump_dir1
Oracle DBA Osama Mustafa gives this nice working
example of network_link in an export/import:
you can import the schema from source database to target
database. One advantage of this network_link option you
don't need export and import as it does the export and
import in single shot from the source to destination.
Also, the file system space is not needed to
accommodate the huge dump files as we can directly
import to target using network_link. The network_link is
very amazing option with data pump because you can take
the backup of source database schema from another
database and you can store in dump files in target
location as well.
Next, I added a TNS entry (File location:
production database in my source database box. Entry
(protocol = tcp)
(host = xxx.xxxx.xxx.xxx)
(port = 1521)
(server = dedicated)
(sid = production)
Note: Make Sure you test the network connection
using tnsping .
Connect to source database using sqlplus and
create a database link to production database with
SQL> create database link
production connect to fred identified by
fred using 'production';
Database link created.
SQL> select * from tab@production
TNAME TABTYPE CLUSTERID
import the fred schema of production database
to source database without dumpfile(Run it From
$ impdp fred/fred directory=network
Next we will export schema from source
database from target machine. You can store
the dump in files. From Source Run (Copy to
dump to folder network)
$ expdp fred/fred directory=network dumpfile=fred.dmp logfile=fred.log
Note that a traditional export to a dmp file, followed by an
FTP may be a faster solution if you network connection has
limited bandwidth. Also, using CTAS (create
table as select) over a database link is faster than
expdp, but it does not copy over the index and