Home » SQL & PL/SQL » SQL & PL/SQL » database link (Oracle 9i)
database link [message #321157] Mon, 19 May 2008 05:03 Go to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi,

I have a database DB1 and I am presently logg into that database.
There is another database DB2.

I have create a dabase link in DB1 like,
CREATE DATABASE LINK dblink
CONNECT TO u1 IDENTIFIED BY pwd
USING 'DB1'

Here u1 is valid account in DB2.

But whenever I am querying a table T1 like,

select * from T1@DB2

It gives the error TNS could not resolve service name.

Please help me in this regard what should I do?

Thanks,
Arindam
Re: database link [message #321161 is a reply to message #321157] Mon, 19 May 2008 05:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Here u1 is valid account in DB2.


If User U1 is a valid account in DB2 then why did you create a database link and asking oracle to using the connect string of Database 'DB1'. Also remember when you say using Alias Name then Oracle will try to resolve the TNS Entry from the server side tns entry and not the client. For more information about database link check the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505

Regards

Raj
Re: database link [message #321163 is a reply to message #321157] Mon, 19 May 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if you create a dblink named "dblink" and call "select * from T1@DB2", it is pretty sure this will not work.

Instead of saying what you do, copy and paste your session.
But before please read OraFAQ Forum Guide, "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.


Regards
Michel

[Updated on: Mon, 19 May 2008 08:13]

Report message to a moderator

Re: database link [message #321208 is a reply to message #321163] Mon, 19 May 2008 07:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am missed the pretty obvious one Cool. I don't know for some reason I assumed the database link is called db2. Good Spot.

Regards

Raj

[Updated on: Mon, 19 May 2008 07:37]

Report message to a moderator

Re: database link [message #321405 is a reply to message #321163] Tue, 20 May 2008 03:20 Go to previous messageGo to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Sorry Michel,

I have typed wrongly..
This time I have tried properly like,

CREATE DATABASE LINK local_dblink
CONNECT TO u1
IDENTIFIED BY pwd
USING 'db2'

SELECT * FROM table1@local_dblink

But it gives the same error:
ORA-12154: TNS:could not resolve service name

What Raj has explained I could not get properly.

Please help me in this regard.
Re: database link [message #321416 is a reply to message #321405] Tue, 20 May 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.

Note that this tnsnames.ora of your db1 server.

Regards
Michel
Re: database link [message #321508 is a reply to message #321416] Tue, 20 May 2008 09:29 Go to previous message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Thanks Michel.
I got your explanation..Thank you very much.
Previous Topic: update query
Next Topic: columns to row
Goto Forum:
  


Current Time: Thu Dec 08 16:18:16 CST 2016

Total time taken to generate the page: 0.11269 seconds