Home » SQL & PL/SQL » SQL & PL/SQL » Database Links
icon4.gif  Database Links [message #250996] Thu, 12 July 2007 02:36 Go to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
hi all,

I have to query between database located on the same/different server. Even after creating database links i could not able to acheive it.

For eg :
1.Database A and B on the same server.

2.Database A and B on the different server.

Suggest me on how to create database links and query the same.


Re: Database Links [message #251001 is a reply to message #250996] Thu, 12 July 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE DATABASE LINK

Regards
Michel
icon4.gif  Re: Database Links [message #251379 is a reply to message #251001] Fri, 13 July 2007 06:56 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
Now i could able to query between databases located on SAME server.

Say u have 2 databases db1 and db2 on same server. To query db2 tables from db1, you need to create a database link

At db1 database as below,
Create public database link CREATE PUBLIC DATABASE LINK "AnyName" CONNECT TO "remoteloginuser" IDENTIFIED by "remoteloginpassword" USING 'db2' on db1 database.



From DB1 database ,
Run this query : select * from table_name@AnyName



HOW TO CREATE A DATABASE LINK BETWEEN DATABASES FROM DIFFERENT SERVER AND QUERY THEM?
Re: Database Links [message #251384 is a reply to message #251379] Fri, 13 July 2007 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SAME WAY.

Regards
Michel


Re: Database Links [message #251387 is a reply to message #251384] Fri, 13 July 2007 07:08 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
But i could nt, i get TNS could not resolve specified identifier error.

the problem is with the connect identifier. how to connect remote databases..
Re: Database Links [message #251393 is a reply to message #250996] Fri, 13 July 2007 07:34 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
first, check if you have proper connection with Remote Database machine and can access specific ports (normally 1521)
on command line:
telnet xxx.xxx.xxx.xxx 1521  <EnterKey>

where xxx.... is the remote database machine's IP

if you see error like cannot connect.... then resolve this problem and if it connects (screen will be blank on my windows machine) it means it is connecting, now enter the appropriate IP address and Port and SID in TNSNAMES.ORA file.
hope this will work.

Re: Database Links [message #251394 is a reply to message #251393] Fri, 13 July 2007 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Sorry, that is not good enough. A server with good security configuration will not allow telnet connections, only ssh. A telnet can fail and yet still have connectivity.
TNSPING would be what I would look at to see if you can "see" the other database.
Additionally, it is NOT a good idea to put anything in double quotes when using Oracle. Database links are the one case where double-quotes does not have any side-effects, but referring to objects with double quotes and anything other than upper case will mean you have to refer to that object in double-quotes every time.
Re: Database Links [message #251446 is a reply to message #251394] Fri, 13 July 2007 11:17 Go to previous message
oralover
Messages: 97
Registered: January 2005
Member
joy_division wrote on Fri, 13 July 2007 07:53
Sorry, that is not good enough. A server with good security configuration will not allow telnet connections, only ssh. A telnet can fail and yet still have connectivity.
TNSPING would be what I would look at to see if you can "see" the other database.



you are absolutely right, agreed 100%, but its initial setup to have working remote database when we are going to evaluate every thing is in order then step-by-step configure to secure it.

thanks for pointing out this and advise.

[Updated on: Fri, 13 July 2007 11:17]

Report message to a moderator

Previous Topic: Procedure Code [Ref Cursor] Help NOT Urgent
Next Topic: Pivot in Oracle
Goto Forum:
  


Current Time: Mon Dec 05 04:59:39 CST 2016

Total time taken to generate the page: 0.18136 seconds