Creating DB Link in Oracle Database

At first we need to add the desired database connection info in tnsnames.ora file. Usually location of tnsnames.ora file in $ORACLE_HOME/network/admin.  We will add connection info as below:

 <connection_name> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <db_host_name>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <remote_db_service_name>)
    )
  )

After adding in the tnsnames.ora file, we will have to create database link in the database. We can do that as below for linking oracle database:

create public database link <db_link_name>
   connect to <remote_db_user_id> identified by <remote_db_user_pass>
    using '<connection_name>';

For the RAC, we should add the connection name in both of the node.

After linbking the database, we ca make query directly to the remoe database as bwloe:

Select * from <table_name>@<db_link_name> ;