Select from two different database

[Iby]

Hello,

Somebody has idea, how can I make a select from two different database?

Thank you in advance,
Ibi

 

[rcurva]

You can create a database link between the two databases.

For example, create a dblink in db2 databse:

CREATE database link db1
CONNECT TO scott IDENTIFIED BY tiger
USING 'db1';

From db2 database, you can issue an SQL like;

SELECT *
FROM table1@db1;


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rcurva]

I hope you are talking about Oracle databases here when you say "Different" databases, Otherwise, you may need to implement Heterogenous services or Generic Connectivity in your setup.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Iby]

Robbie,

Thank you for your help. One thing what I still don't understand that what does it mean that statement between '' and after the USING?

 

[rcurva]

This is the service name of the remote database where you want to connect to. This name must be listed in the tnsnames.ora in your server where the database db2 resides.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Iby]

Thank you Robbie, now it's absolute clear!!
Kind Rgds.
Ibi

 

[Iby]

Sorry Robbie, one more question: Why did I get this msg. when I would like to run a select but before the link creating was successful?

"ORA-02019 connection description for remote database not found"

 

[rcurva]

It could be that your remote database is not properly configured in your tnsnames.ora.

Make sure that you can connect/tnsping to your remote database using the service name in the tnsnames.ora.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rcurva]

You might be using another user to access the remote database from the one that created the db link. In this case, you should create a PUBLIC database link, and not a PRIVATE one.

CREATE PUBLIC DATABASE LINK db1
CONNECT TO scott IDENTIFIED BY tiger
USING 'db1';

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Iby]

Thank you very mutch Robbie, your help was great!!!
Ibi