ORA-12154: TNS: could not resolve the connect identifier specified

What
Database link is an option to connect to a remote database from another database. By using this database link option one can connect to remote database as authorized database user and can perform the operations as in the direct connection.

Database link is of two types

1. Public
2. Private

How
Add a entry in tnsnames.ora file to connect to “demo” database

demo=
(
DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = DEMO))
)

Create a database link by using the following syntax
Database Link – if PUBLIC keyword is mentioned the DB link will be a Public DB link otherwise private

CREATE [PUBLIC] DATABASE LINK demo_dblink
   CONNECT TO scott IDENTIFIED BY tiger
   USING ‘demo’;

If it is showing error  ORA-12154: TNS:could not resolve the connect identifier specified .Normally the database should refer the tnsname.ora file for connection strings, but not every time. It is because the Database not able to resolve the connection string, to work around that follow the below method

CREATE DATABASE LINK demo_dblink
CONNECT TO scott IDENTIFIED BY tiger
USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DEMO) ))’;

Check the created db links here in the data dictionaries

SELECT * FROM all_db_links
SELECT * FROM user_db_links
SELECT * FROM dba_db_links

If the entry appear in results then the DB Link has been created Now can perform the granted actions on the remote database with the DB link

SELECT * FROM dual@demo

The above query will fetch the data from demo database. One can perform any type action/command for which the user (scott) have grants in the demo database