Oracle Database Links

Oracle Database Link is used to establish connections between two database.
The purpose of database link is used to exchange the data between 2 databases.

Types Of Database Links: 

Public Database Link:  All users in a database can use it.

Private Database Link: Belongs to a specific database schema. Only the owner of a private database link can use it.

Global Database Link:  Defined in an OID or Oracle Names Server. Anyone on the network can use it.

Prerequisites  To Create Database Link:

• Need to copy target database tns entry in source database tns file.
• Need to grant CREATE DATABASE LINK to user.

Create/Drop Private DBLink:

CREATE DATABASE LINK ABC<LINK_NAME> CONNECT TO SCOTT IDENTIFIED BY TIGER USING  'ORADB<TARGET TNS>';

DROP DATABASE LINK ABC;

Create/Drop Public DBLink:

CREATE PUBLIC  DATABASE LINK ABC<LINK_NAME> CONNECT TO SCOTT IDENTIFIED BY TIGER USING  'ORADB<TARGET TNS>';

DROP PUBLIC DATABASE LINK ABC;


Close Database Link:

ALTER SESSION CLOSE DATABASE LINK ABC<LINK_NAME>;

Drop database link when you are not the owner of  the database link:

When you are the owner of the link you can drop it without any problem but if you are a DBA and want to purge the database . Lets say after restoring the TEST database from live database you can use the proxy user feature so you can connect to the database link owner  without knowing or changing it's password (Here Michel is the DBA).

SQL>CONNECTED test/test;
Connected.

TEST>CREATE DATABASE LINK mika@loop CONNECT TO TEST IDENTIFIED BY test USING 'MIKA';
Database Link Created.

TEST>CONNECT Michel/Michel;
Connected.
MICHEL>ALTER USER TEST GRANT CONNECT THROUGH MICHEL;
MICHEL>CONNECT michel[test]/michel;
Connected.
TEST>SHOW USER;
USER is "TEST".
TEST>DROP DATABASE LINK mika@loop;
Database Link Dropped.
TEST>CONNECT michel/michel;
Connected.
MICHEL>ALTER USER TEST REVOKE CONNECT THROUGH MICHEL;

User Altered.

Creating Database Link With Encrypted Password:

CREATE DATABASE LINK ABC<LINK_NAME> CONNECT TO SCOTT IDENTIFIED BY VALUES 'DEA2G0D1A57B0071057A11DA7A' USING  'ORADB<TARGET TNS>';

Views Related To Database Links:

• DBA_DB_LINKS
• ALL_DB_LINKS
• USER_DB_LINKS