CODEWITHSUNDEEP
oracle-database
user1654046
user1654046

Reputation: 9

Connect two Oracle Databases

I am not good at English please help me

I have two problems that I need help with.

I have two databases in one Oracle Server at IP 10.10.10.92

Table One has two columns CustumerID and CustumerName (Table One belongs to Database 1 named DB1). Table Two has two columns CustumerID and Job (Table Two belong to Database 2 named DB2)

  1. If I am using Oracle SQL Developer or TOAD for Oracle to query statements
    How can I connect from two table above?

  2. If I am Using SQL Server 2005 to link two Oracle Database to query statements
    How can I connect from two table above?

Do I need to create two linked server or one linked server?

Upvotes: 0

Views: 12192

Answers (1)

João Barreto
João Barreto

Reputation: 184

If I understood correctly your question - you have Database 1 (DB1) and Database 2 (DB2) in the same server, each one of them has a customer table, right?

You can "connect" both database using a Database Link:

http://psoug.org/reference/db_link.html

I will assume that your users are named hr1 and hr2, respectively for DB1 and DB2.

Then it would be something like (in DB1):

CREATE DATABASE LINK to_db2 CONNECT TO hr2 IDENTIFIED BY hr2 USING DB2;

and in DB2

CREATE DATABASE LINK to_db1 CONNECT TO hr1 IDENTIFIED BY hr1 USING DB1;

For this to work you got to have both databases with the right TNS, or else you got to use a connection string instead of the database service. After that you can query the other table:

SELECT customer_name FROM customer_table@to_DB2 --connected to DB1.

EDIT: After your edit: 1. That would be the answer above, regardless of the IDE you use. 2. Can you be more clear? You could create a view in DB1 to access DB2 and access that view from SQLServer.

Upvotes: 4

Related Questions