OraFAQ Forum: Server Administration » Data from Two Oracle Databases

Home » RDBMS Server » Server Administration » Data from Two Oracle Databases

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data from Two Oracle Databases [message #54206]

Mon, 04 November 2002 22:31

HC Yung
Messages: 2
Registered: November 2002

Junior Member

Hi,

How can I retrieve data from two seperate Oracle Database (resident on different database server) with a common primary key by using ASP.

HC.

Report message to a moderator

Re: Data from Two Oracle Databases [message #54212 is a reply to message #54206]

Tue, 05 November 2002 04:25

sai sreenivas jeedigunta
Messages: 370
Registered: November 2001

Senior Member

Hello,
From the native database side, u can use database links for getting the data from two databases, Similarly in case of ASP , u create two DSN's / 2 Connectstrings and use them alternatively..

sai

Report message to a moderator

Re: Data from Two Oracle Databases [message #54219 is a reply to message #54206]

Tue, 05 November 2002 10:26

Trifon Anguelov
Messages: 514
Registered: June 2002

Senior Member

Here is what you have to do:

1. Create TNS service names on the "different database server" for the both Oracle db servers, eg.

ORA1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1.domain.com)(PORT = 1521))
)
(CONNECT_DATA = (SID = SID1)
)
)

ORA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2.domain.com)(PORT = 1521))
)
(CONNECT_DATA = (SID = SID2)
)
)

2. Create database links to those servers, eg.

CREATE PUBLIC DATABASE LINK ORA1
CONNECT TO SCOTT
IDENTIFIED BY TIGER
USING 'ORA1'
/

CREATE PUBLIC DATABASE LINK ORA2
CONNECT TO SCOTT
IDENTIFIED BY TIGER
USING 'ORA2'
/

3. On this "different database server" in init.ora file add GLOBAL_NAMES = TRUE. Restart the instance.

4. Now you can issue queries to both remote servers by:

SELECT * FROM user.table@ORA1;

Hope that helps,

clio_usa
OCP - DBA

Visit our Web site