In a single database, users can grant permissions to objects they own to other users. However, if you have more than one database, it is impossible for users to interact between one database and another unless we have a Database Link, which is the link between one database and another. You may need this a lot during your work, as you may have More than one database needs to be linked together. Let's assume that we have a database called OBAY and another called ORCL.

SELECT NAME FROM V$DATABASE;

Now we need to link the OBAY database with the ORCL database, and let's assume that the user TEST in the OBAY database needs to run a query on the EMPLOYEE table owned by the VBS user in the ORCL database, then we need to run a Database Link between the user TEST in the OBAY database and the user VBS in the ORCL database.

But before creating the Link Database, it must be noted that the user who creates the Link Database must have the CREATE DATABASE LINK permission.

Now the user TEST in the OBAY database will create a Database Link between him and the user VBS in the ORCL database.

First: The user TEST connects and makes sure that he has the CREATE DATABASE LINK permission.

SELECT * FROM USER_SYS_PRIVS WHERE PRIVILEGE='CREATE DATABASE LINK'; 

In the previous step, we first made sure that we are working on the OBAY database and secondly we made sure that the user TEST has the CREATE DATABASE LINK permission.

Secondly: The user TEST tests the Oracle Net Service aliases found in the tnsnames.ora file to ensure the integrity of the connection to the other database, which is here ORCL.

TNSPING ORCL 

Third: The user TEST creates the Database Link.

CREATE DATABASE LINK TESTVBS CONNECT TO VBS IDENTIFIED BY VBS USING 'ORCL'; 

The user TEST has created a Database Link called TESTVBS through which the user TEST can connect to the user VBS in another database called ORCL.

Fourth: Now the user TEST can query the EMPLOYEE table owned by the user VBS in the ORCL database.

SELECT * FROM EMPLOYEE@TESTVBS; 

Sometimes the name of the Database Link is accompanied by the name of the table, which is long. It is better to create a synonym to facilitate and shorten writing phrases, especially those that are written repeatedly.

CREATE SYNONYM EMP FOR EMPLOYEE@TESTVBS; 

But it should be noted that the user needs the CREATE SYNONYM privilege to create the synonym.

Now the query phrase can be written as follows:

SELECT * FROM EMP;

It is worth noting here that we say that you can perform DML operations through the Database Link, which are (DELETE & UPDATE & INSERT & SELECT), but it is impossible to perform DDL operations such as (CREATE & ALTER & DROP).

PUBLIC DATABASE LINK:

What we did in the previous steps is to link the user TEST in the OBAY database with the user VBS in the ORCL database via a DATABASE LINK called TESTVBS.

But can any other user in the OBAY database use the TESTVBS DATABASE LINK? The answer is simply no, because the Link Database that we created is specific to the user

TEST and not to all users and it is called PRIVATE DATABASE LINK.

In order for all users in the OBAY database to be able to connect to the VBS user in the ORCL database, we need to create a PUBLIC DATABASE LINK.

Of course, to create this type, we need the CREATE PUBLIC DATABASE LINK permission.

SQL> CONN / AS SYSDBA

SQL> GRANT CREATE PUBLIC DATABASE LINK TO TEST;

SQL> CONN TEST/TEST

SQL> CREATE PUBLIC DATABASE LINK PUBTESTVBS CONNECT TO VBS IDENTIFIED BY VBS USING 'ORCL'; 

The database administrator granted the user TEST the CREATE PUBLIC DATABASE LINK privilege and then the user TEST created a PUBLIC DATABASE LINK

called PUBTESTVBS, now all users in the OBAY database can use PUBTESTVBS DATABASE LINK.

To query the DATABASE LINK in the database:

DBA_DB_LINKS

USER_DB_LINKS

ALL_DB_LINKS

V$DBLINK

Of course, the DATABASE LINK can be deleted.

DROP DATABASE LINK TESTVBS; 

You can also delete the PUBLIC DATABASE LINK.

DROP PUBLIC DATABASE LINK PUBTESTVBS; 

Of course we need permission to DROP PUBLIC DATABASE LINK.