need to connect to two different db from sqlplus

Question

I need to take information from two different data bases.

select * from TABLE_ONDB2 where column_on_db2 in ( select column_on_db1 from TABLE_ONDB1 );

Problem is both are on different db instances so I am not able to figure out how to put table names and column names etc.

I hope my question is clear.

Answer 1

You'll need a database link for this. See this

Answer 2

I'd try to do it with a Database Link:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_concepts002.htm

That is, however, not a SQL*Plus feature. It works by makeing a connection from DB2 to DB1 (the database is doing that).

You can then query both tables from DB2 with the '@db-link' name notation. e.g.,

select *
  from TABLE_ONDB2
 where column_on_db2
    in (select column_on_db1 from TABLE_ONDB1@DB_LINK_NAME);
                                             ^^^^^^^^^^^^^

The benefit is that you can access the table in all different ways, also as a join.