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

 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