If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > ORA-12154: TNS:could not resolve the connect identifier specified

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 14
Red face ORA-12154: TNS:could not resolve the connect identifier specified

Getting error while executing following query-

Quote:
SELECT * FROM Emp@Rept
Error: ORA-12154: TNS:could not resolve the connect identifier specified
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,495
ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.wordpress.com/20...2154tns-03505/
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2006
Posts: 49
In this case it is a server side error, not a client error as previously reported. Because the error occurred on a dblink, the error is at the db server.

You are querying a table in a remote database. The Emp@Rept means you are querying table Emp in the data source identified by dblink Rept. The names are not case sensitive. Rept is the name of a dblink that points to an entry in file tnsnames.ora on the database host, and has nothing to do with the client.

First, log into the database using sqlplus or OEM and look at the dblink to see what it points to. The properties might include a TNS string, username, and a password.

Second, log into the database server where the error occurred as user oracle (or who ever owns the database home) and look at file $ORACLE_HOME/network/admin/tnsnames.ora. You must find an entry that corresponds to the dblink's properties.

Third, use the "tnsping" utility to test the tnsnames.ora entry.

If you have global_names=true then the name of the dblink "Rept" must match the name of the entry in tnsnames.ora.

If you have file sqlnet.ora (in the same directory as tnsnames.ora) look for the parameter sqlnet.default_domain. When the dblink is accessed Oracle appends the domain to it, so the entry in tnsnames.ora would be of the form Rept.yourdomain.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 1
Actually, it is *STILL* a client-side issue. When using a dblink, the database owning the link is acting as a client to the remote database. No magic, no hocus pocus, no splitting of hairs over terminology. You just have to keep in mind who is the client for any particular process. No different than looking at classing 3-tier architecture. To the guy sitting at his computer using a web browser, his computer is the client, the app server that is serving up the web pages is the server. But as far as the database is concerned, the app server is the client. And if that database is using a dblink to yet another database ....
Reply With Quote
Reply

Tags
ora-12154

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On