What is Database link(DB link), Private and Public DB link

A database link enables a one-way connection to a remote database from a local database. The link is one-way only. The remote database users can’t use this link to connect to the local database—they must create a separate database link for that.

A database link allows us to gain access to a different database though a remote database user account; we don’t have to be a user in the remote database. Our privileges on that database will be identical to the privileges of the user account you use when creating the database link. Database links are useful when we want to query a table in a distributed database or even insert data from another database’s table into a local table. Database links allow users to access multiple databases as a single logical database.

We can create private and public database links. In the following sections, we’ll look at examples of how to create both types of database links.

Creating a Private Database Link

A private database link is owned by the user that creates the link. In the following statement, the SYSTEM user creates a private database link. The database link enables a connection to the remote database using the hr user’s username and password in that database.

SQL> CONNECT system/system_passwd@finance
Connected.
SQL>
SQL> CREATE DATABASE LINK MONITOR
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'monitor';
Database link created.
SQL>

Note: To create a database link, a user must have the CREATE PRIVATE DATABASE LINK privilege or the CREATE PUBLIC DATABASE LINK privilege in the local database.

After the link is created, the SYSTEM user can query the hr.employees table in the remote
database.

SQL> SELECT COUNT(*) FROM hr.employees@monitor;
COUNT(*)
----------
107

In the preceding statement, note that the database link’s name is MONITOR, which is the same as the remote database’s TNS name alias (Oracle Net Service alias), but it could be anything we want. The CONNECT TO . . . IDENTIFIED BY clause means that the user of this database link will use that username and password to enter the remote database. The USING 'monitor' clause simply specifies the TNS name alias for the linked remote database.
Because this is a private database link, only the SYSTEM user can use it. When the hr user tries to use this link to a remote database, this is what happens:

SQL> CONNECT hr/hr;
Connected.
SQL> SELECT count(*) FROM hr.employees@monitor;
select count(*) from hr.employees@monitor
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL>

Creating a Public Database Link

A public database link, unlike a private database link, enables any user or any PL/SQL program unit to access the remote database objects. The creation statement is very similar to that for a private database link. We just add the PUBLIC keyword to the CREATE DATABASE LINK statement:

SQL> connect system/system_passwd as sysdba;
Connected.

SQL> CREATE PUBLIC DATABASE LINK MONITOR
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'monitor';
Database link created.
SQL>

Once the public MONITOR link is created, any user can log into a remote database using that
link. In the following example, the user tester uses the public database link to query the remote database, MONITOR.

SQL> CONNECT tester/tester1;
Connected.
SQL> SELECT COUNT(*) FROM hr.employees@monitor;
COUNT(*)
----------
107
SQL>

Note: We can create a public database link if several users require access to a remote Oracle database from a local database. Otherwise, create a private database link, which will allow only the owner of the private database link to access database objects in the remote database.