We help IT Professionals succeed at work.

Copy data from multiple Oracles DBs and insert it all to one Oracle DB

KingMooBot
KingMooBot used Ask the Experts™
on
I would like to know the best way to copy data from many different tables, databases, and servers to one database (with VB.NET). I was previously loading many CSV reports from the sources using oracle 11g's "bulkcopy" but this proved to be cumbersome.

I also need to modify some of the data before (or after I guess...)  it's inserted to its destination.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
Remote materialized views are the quickest and simplest to setup.

Something like:

1) Setup remote database link on db2 to db1

2) create materialized view tab1 as select * from tab1@db1

Top Expert 2009
Commented:

Author

Commented:
thanks that's a perfect solution!
Top Expert 2009

Commented:
For a one time copy, you can also use the simple:

On db2:

  create table tab1 as select * from tab1@db1


Materialized views, however, allow you to do updateable views that replicate back to parent, and/or faster refreshes when using materialized view logs.

Cheers.