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.

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

dpbradley
– dpbradley

2010-08-31 13:53:44 +00:00
Commented Aug 31, 2010 at 13:53 — dpbradley
– dpbradley, Commented Aug 31, 2010 at 13:53

Markus Winand · Accepted Answer · 2010-08-31 13:55:29Z

5

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.

answered Aug 31, 2010 at 13:55

Markus Winand

8,8541 gold badge38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hemant Over a year ago

thanks this what I am looking for. That link is really good :-).

Collectives™ on Stack Overflow

need to connect to two different db from sqlplus

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related