05-23-12, 06:09 #1Registered User
- Join Date
- Jan 2012
ORA-12154: TNS:could not resolve the connect identifier specified
Getting error while executing following query-
SELECT * FROM Emp@Rept
05-23-12, 08:49 #2Registered User
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.
There are no stupid questions, but there are a LOT of Inquisitive Idiots.
05-30-12, 09:54 #3Registered User
- Join Date
- Jul 2006
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.
11-23-12, 19:39 #4Registered User
- Join Date
- Nov 2012
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 ....