The connect string to reference the remote database can either be a TNS name or a URL in the form ‘Hostname:Port/Servicename’. Sometimes it is difficult to get the TNS name approach running out of the box – there are plenty things which can cause errors like ORA-12514 (TNS:listener does not currently know of service requested in connect descriptor), ORA-12541 (TNS:no listener) or ORA-12154 (TNS:could not resolve the connect identifier specified). In any case, such errors occur only when actually accessing the remote database – you can create any database link with any parameters, independent whether they are valid or not. Try
CREATE DATABASE LINK BogusLink CONNECT TO BogusUser IDENTIFIED BY Secret USING 'My remote database';
CREATE DATABASE LINK succeeded.
SELECT * FROM user_tables@BogusLink;
ORA-12154: TNS:could not resolve the connect identifier specified
As you see, the creation of the database link succeeds, but when accessing the remote database an error is reported.
A query which might be useful (especially if you encounter some strange behaviour) in investigating issues with database links is to query the user and the database where your current session is running:
SELECT user || '@' || global_name AS MyUser FROM global_name;
Especially if working with multiple databases, it is good to know on which instance you are currently working. So make sure that you are really typing in the right session by using the above statement. BTW: Make sure that you are in the session you think you are! Did I mention it already? Really make sure you work in the proper session!!