Oracle : Database Links - Create, Use and Drop

A database link is an important schema object in the oracle database, we should know details about it in order to implement it in real-time applications,

Assume you have two different applications with two different databases, and while generating reports, you need to fetch and merge data from both the database, it’s a good idea to use a database link, i.e. connecting 2 databases together

We will discuss more

Generally, there are 2 types of database links,

• Public 
• Private 

Public - database link, which can be accessed by all users in the databases, 

Private- database link, which can be accessed by only specific users

 The syntax for creating database links is quite similar 

 create [Public/private] database link <link name>

 Explanation with example 

 Assume below details 

	Database	Schema	Connection link
Source details	technodb	tech_owner	technodb
Target details	asgard	tech_as_owner	asgard

 We will be creating database link asgard_link in database technodb, connecting to the asgard database, and will try to fetch data from a remote database

Create a private database link

Connect to the database using userid tech_owner and execute create database link command, before executing the command remember that, you need to have to create database link privileges assigned.

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER';

 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     CREATE DATABASE LINK

You can see, tech_owner has create database link privileges

Now create a private database link 

SQL> show user

USER is "TECH_OWNER"

SQL> create database link asgard_link

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard';

 

Database link created.

 

Here,

asgard_link – is the database link name

tech_as_owner is a remote user and its password

‘asgard’ – connection string to connect remote database 

Verifying if the database link is working

Execute select query to fetch data from remote database using db link, if returns value, it working fine or in case of error, need to rectify

 SQL> show user

USER is "TECH_OWNER"

SQL> select sysdate from dual@asgard_link;

SYSDATE

---------

29-AUG-22

 

Create a public database link

Similarly, we can create a public database link using the below statements, make sure you have create public database link  privilege granted

SQL> grant create public database link to tech_owner; 

Grant succeeded.

SQL> show user

USER is "TECH_OWNER" 

SQL> create public database link asgard_link_pub

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard'; 

Database link created.

 Now try accessing it through different accounts, you will be able to fetch the data,

SQL> show user

USER is "TECH_OWNER"

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22

SQL> conn /as sysdba

Connected.

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22 

How to drop database link

to drop the private database link, you need to connect using a username account and then execute the drop command,

SQL> show user

USER is "TECH_OWNER"

SQL> drop database link ASGARD_LINK; 

Database link dropped.

 To drop the public database link, you need to mention the public identifier, like below

 SQL> drop public database link ASGARD_LINK_PUB ; 

Database link dropped.