Database links use a connect descriptor ("my_link") that needs to be defined in the tnsnames.ora file in the ORACLE_HOME/nework/admin directory. Look up the definition of "my_link" in this file on the source database - does the same definition exist in the tnsnames.ora file of the target ?
I always thought that db_link uses the connect descriptor 'some_db' ( from the USING clause) and not the db_link names itself.
Here is how the db_link has been defined:
CREATE DATABASE LINK my_link
CONNECT TO some_user
IDENTIFIED BY <PWD>
And "some_db" is both in the source systems tnanames.ora as well as the target system.
I am able to connect to "some_db" using the "@" syntax which tells me that tnsnames.ora entry exists.
Apologies for my ignorance but I do not think an entry for "my_link" has to be in the tnsnames.ora file. Required entries for the databases exist in the tnsnames.ora.
You are correct - I meant to state "some_db" instead of "my_link".
What is the output of "tnsping some_db" from the target database ?
The database link should be cerated and work find after the import. The commonest cause of this type of problem i've found is that the value of db_domain is different in the 2 systems.
Can you give us the output of select * from dba_db_links?
$ tnsping some_db
TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 17-SEP-2013 13:29:06
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <host-name>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = some_db)))
OK (0 msec)
select * from dba_db_links
owner db_link username host created
<owner> my_LINK <user_id> some_db 8/8/2013 5:42:58 PM
Sorry that I have to remove the actual info!
When you do the test are you logged in as the owner of the db link? Unless the database link is public only the owner of the link can use it.