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 [email protected]
I get the following error:
: (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.