Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-12514 when trying to create db link

I am having a problem creating a db link. It creates successfully but when I run a test query I get the ORA-12514 error.

I am having a problem creating a db link. Here is the ddl I use to create it:

 CREATE DATABASE LINK 1234.xxxx.COM CONNECT TO ttt IDENTIFIED BY <ENTER USER PASSWORD HERE> USING '1234.xxxx.COM' /

It creates successfully; however, when I run the following test query:

 select sysdate from dual@1234.xxxx.com

I get the following error:

 [1]: (Error): ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

Everything seems to be correct in the service_name as I can tnsping 1234.xxxx.COM from my box. I can also connect to the db using the connection 1234.xxxx.com and id used to create the link. Moreover, the ttt id is the schema owner so it should not have any problems with security. Thanks in advance.
The error you are getting is very simple to fix. It simply states that the SERVICE_NAME in your TNSNAMES.ORA file for that TNS entry does not match any SERVICE_NAME defined for your Listener you are trying to contact. The hard part of this that most people don't realize is that the TNSNAMES.ORA file that database links use is in the default network config directory, typically $ORACLE_HOME/network/admin. Your session can use any TNSNAMES.ORA file stored in any location as long as your TNS_ADMIN environment variable can find that TNSNAMES.ORA file. But database links do not use this TNS_ADMIN environment variable. Make sure that your TNSNAMES.ORA file in $ORACLE_HOME/network/admin is configured properly.

Dig Deeper on Oracle error messages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close