Bulk Row Transfer between Oracle Databases with a Select Filter

Question

Basically, I'm trying to selectively copy a table from one database to another. I have two different [Oracle] databases (e.g., running on different hosts) with the same schema. I'm interested in a efficient way to load Table A in DB1 with the result of running a select on Table A in DB2. I'm using JDBC, if that's relevant.

Answer 1

Use a database link, and use create table as select.

create database link other_db connect to remote_user identified by remote_passwd using remote_tnsname;

create table a as select * from a@other_db;

Answer 2

If the databases are from the same vendor they usually provide a native way to make a view of a table in another database. in which case, a "select into" query will do it no problem

Oracle, for example, has the database link which works pretty well.

Outside of that you are going to have to make a connection to each database and read in from one connection and write out to the other.

There are tools like Oracle's ODI that can do the legwork, but they all use the same read in, write out model

Answer 3

You may not even need to move that data. Maybe you can just select across the database link.