I have a question about DataPump API. I was wondering if it was possible to export a table directly in another database.
My problem is like this. I have two databases. Let's say db_src and db_dst. I have a database link in one direction. db_src can put data in db_dst using an INSERT like this
INSERT INTO dst_table@db_dst ....
But I can not do in the destination database a SELECT from the source.
Every day I have to copy the content from the source database to the destination database. I was wondering if we can do this directly using DATAPUMP. INSERT INTO the remote database take quite some time. I also have to syncrhonize the structure and the indexes manually. Datapump can probably do that for me. So, I was thinking, is there a solution to use datapump to do that without creating a file on a folder? Im using Oracle 10g.
Data Pump import using the NETWORK_LINK parameter will avoid the need for a file, but if you cannot "select * from dst_table@db_src" in the destination database, then data pump network import will not help, either, since it does a similar "select from source over database link" approach to move the data.
Data Pump import still performs INSERTs, so just switching to data pump may not solve a performance issue.
Data Pump does support what you want to do, but you need to have a db link created on the target pointing to the source. It seems like you have it the other way around. This is not supported for DataPump.
If you are on the target or dest database and have a db link pointing to the source db_link_source then use this command:
impdp user/password tables=userx.tablex network_link=db_link_source ...
This will import that table from the source into your local destination database.
Network link is supported in the Data Pump api. Like the post prior to this one says, network link is a pull, never a push, so it can be done, but the network link needs to be defined on the target/destination pointing to the source.
Thank you all for your advice.
Sorry Harry, but I don't know any other sync method. If DataPump needs the db link the other way around, I will stick to INSERT INTO dest_table@remote_link :(
Thank you again :)
thanks for all good info.. during NETWORK_LINK pull do we need to drop destination table before pulling from source?
or we use table_exist= replace option?
all objects connected to table will also get import?
Edited by: khallas301 on Dec 13, 2012 11:40 AM