How to Create and Use Oracle Database Link ( DB Link )

We of-course heard the keyword DB LINK from DBAs. Every developer should know about this to ease their day to day DB data handling tasks. 

Okay.. Let see in detail What is DB link ?, How to create DB link? and How perfectly use DB link?  in this article.  

About DB Link :-

Database Link is an object can be created in one database; by which we can access other database objects such as tables, views etc.,

For example :-  If we create DB Link in DEV DB to connect with QA DB, then we can access QA database from DEV DB.  We can do SELECT, INSERT, UPDATE & DELETE statements for QA tables from logging into DEV DB.

Prerequisites to create DB link :-

Database should have the below two accesses to create DB link 

          a)  CREATE DATA BASE LINK   ( at local DB )   

               to create private DB LINK

                     [OR]

               CREATE PUBLIC DATA BASE LINK ( at local DB )

               to create public DB LINK

               

          b)  CREATE SESSION ( at remote DB )

How to create DB link :-

SYNTAX 1  ( To create PRIVATE  DB  Link )

CREATE DATABASE LINK   DB_LINK_NAME  USING  SERVICE_NAME 

SYNTAX 2  ( To create PUBLIC   DB  Link )

CREATE PUBLIC DATABASE LINK   DB_LINK_NAME  USING  SERVICE_NAME

SYNTAX 3 (  To create PUBLIC  DB LINK to access particular schema  )

CREATE PUBLIC DATABASE LINK   DB_LINK_NAME  

CONNECT TO  SCHEMA_NAME

IDENTIFIED BY   PASSWORD

USING   SERVICE_NAME

DB_LINK_NAME  ->   Name to connect with remote DB

SERVICE_NAME  ->   Remote DB service name

SCHEMA_NAME   ->  User name of Remote DB schema name

PASSWORD         ->  Password to connect remote DB schema

How to use DB link :-

SELECT FROM TABLE_NAME@DB_LINK_NAME

DELETE FROM TABLE_NAME@DB_LINK_NAME

TABLE_NAME  ->  Table name of a remote DB 

How to see existing DB links in the current schema/DB :-

SELECT  *   FROM     DBA_DB_LINKS

( List all Database Links in the local database )

SELECT  *   FROM     ALL_DB_LINKS

( List all Database Links accessible to the current user )

SELECT  *   FROM     USER_DB_LINKS

( List all Database Links owned by current user )

How to Drop Data base link 

DROP DATABASE LINK  DB_LINK_NAME

DROP PUBLIC DATABASE LINK DB_LINK_NAME

( Think twice before you execute DROP DB LINK statement; since it will drop the access to other DB's object )

Your feedback about this article is most welcome. Please feel free to comment below.