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
|
|
Database |
Schema |
Connection link |
|
Source details |
technodb |
tech_owner |
technodb |
|
Target details |
asgard |
tech_as_owner |
asgard |
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
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.
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.
Database link dropped.
