Oracle Database link

What is a database link?

A database link will come in handy when we need to access data from another database. The target database need not be an Oracle database, in that case heterogeneous services needs to be enabled.

What are the prerequisites for creating a database link?

1. The create database link privilege must be available for the ID with which the database link needs to be created.
2. The Source and target database servers must have the tnsentry of both the databases added in their machines, oracle net must be installed in both the servers.
3. You must have create session privilege on the target database for the ID which you are using.

Syntax for creating database link:




Example for database link creation:

Lets imagine you got 2 databases, HCMPRD and FINPRD. You want to select the details from PS_JOB table present in HCMPRD from FINPRD, to do this we need to follow the below steps after adding the tnsentry in both the database server.

1. Create an ID in HCMPRD as HCMFIN and provide select access to the PS_JOB table (We do this to prevent the user from FINPRD to access other tables in HCMPRD.)

2. Create the database link FINPRD using the below syntax,

CREATE DATABASE LINK "HCMPRD"  CONNECT TO "HCMFIN" IDENTIFIED BY "HCMFIN" USING 'HCMPRD';

3. Create synonym for the PS_JOB table to access from FINPRD, use the below syntax for the same,

CREATE OR REPLACE SYNONYM "SYSADM"."PS_JOB1" FOR "SYSADM"."PS_JOB"@"HCMPRD";

Now we will be able to access the PS_JOB table in HCMPRD from FINPRD by selecting from the synonym PS_JOB1.