What is the difference between public and private database links? How to create them?

This document describes what is Public and Private database links and how to create them.

Difference between Public and Private database link:

Please Click Here to know about dblinks.

Create Private database link:

Assumption: We have two databases GOLD and SILVER in two different hosts. SILVER has table called "TARGET". We want to access TARGET table from GOLD database by using database link.

Syntax: create database link <database link name> connect to remote_username identified by remote user password using 'tns_service_name';


SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string GOLD

SQL> show user

USER is "ADAM"

SQL> create database link gold_to_silver connect to chris identified by welcome using 'SILVER';

Database link created.

SQL> select * from target@gold_to_silver;

       SNO    NAME       SALARY

----------     ----------      ----------

       101     KUMAR      5000

Verification: Connect to target database and verify target table.

SQL> show parameter db_name

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name      string SILVER

SQL> conn chris/welcome

Connected.

SQL> select * from target;

       SNO NAME       SALARY

---------- ---------- ----------

       101 KUMAR      5000

Create Public database link:

Syntax: 

create public database link < database link name> connect to remote user identified by remote user password using 'tns_service_name';

SQL> show user
USER is "ADAM"

SQL> create  public database link GOLD_TO_SILVER_PUBLIC connect to chris identified by welcome using 'SILVER';

Database link created.

SQL> select * from target@GOLD_TO_SILVER_PUBLIC;

       SNO   NAME       SALARY
----------    ----------       ----------
       101    KUMAR      5000

SQL> show user
USER is "SYS"

SQL> select * from target@GOLD_TO_SILVER_PUBLIC;

       SNO     NAME       SALARY

----------      ----------       ----------

       101      KUMAR      5000

Thanks for your patience to view this post...........