Question: How does the network_link syntax
of data pump allow me to export and import between databases?
Answer: First,
note that
expdp and impdp is only one of
many options to migrate tables between databases.
The
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.
Also see:
direct export/imports between databases
.
To
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, y
ou
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
NETWORK_LINK=source_database_link
DUMPFILE=network_export.dmp
Oracle DBA Osama Mustafa gives this nice working
example of network_link in an export/import:
With network_link
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:
$ORACLE_HOME/network/admin/tnsnames.ora) for
production database in my source database box. Entry
as below:
production =
(description =
(address =
(protocol = tcp)
(host = xxx.xxxx.xxx.xxx)
(port = 1521)
)
(connect_data =
(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
fred user
SQL> create database link
production connect to fred identified by
fred using 'production';
Database link created.
SQL> select * from tab@production
2 ;
TNAME TABTYPE CLUSTERID
------------------------------ -------
----------
test1 TABLE
import the fred schema of production database
to source database without dumpfile(Run it From
Source)
$ impdp fred/fred directory=network
logfile=fred.log
network_link=production
Nest
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
network_link=production
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
constraint definitions.
