Question: How do you export a table directly to
a remote database? I don't want to use expdp to create
an intermediate .dmp file to transfer to the remote database
and I understand that expdp can export and import using a db
link. If so, how do you invoke a direct export to
another database?
Answer: First, note that expdp/impsp is only one of
many options to migrate tables between databases. In
expdp and impdp you can use the
network_link parameter provided that you have defined a
database link:
create database link
my_link
connect to
fred
identified by
flintstone
using
testdev;
One the
network_link is create you have two ways to directly
export/import database between systems:
1:
Expdp from the source database to the destination (remote)
database. Note that using expdp with the
remote link requires that you specify a dmp file:
expdp fred/flintstone@testdev
tables=customer network_link=my_link
directory=TEST_DIR dumpfile=/tmp/cust.dmp logfile=mylog.log
2: Use
impdp with the network_link parameter from the
destination database.
impdp fred/flintstone@testdev
tables=customer network_link=my_link directory=tempdir
logfile=importcust.log remap_schema=fred:test
Both the
local and remote users require the exp_full_database
role granted to the user ID. Also note the
"parallel" parameter which speeds-up the export/import
operations.
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.
