Error creating linked database to oracle

oracleoracle-11g-r2

CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD';

OPT=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

I get following error:

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

I can connect using sqlplus though and can get response from tnsping.

Best Answer

In a database link, you should use the net_service_name, i.e. the OPT string, instead of SERVICE_NAME identifier.

EDIT:

Example:

cat tnsnames.ora|grep TEST
TESTNS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=THE_HOST)(Port=THE_PORT)))(CONNECT_DATA=(SID=THE_SID)))

oracle@hostname> export ORACLE_SID=ANOTHER_SID
oracle@hostname> sqlplus username
Enter password: 
Connected to:
<few lines of sqlplus banner>
SQL> create database link lll connect to <the_username> identified by <the_password> using 'testns';
Database link created.
SQL> select * from global_name@lll;

GLOBAL_NAME
--------------------------------------------------------------------------------
<the_global_name>

SQL>
Related Question