Oracle Database Links (DB Link)

[debasisdas]

DATABASE LINK
===============
A database link is a path through which a remote user in another database can connect to any other database. Once created database link exists as an object in the user schema.

DATABASE LINK -3 TYPES
=============== ========
PRIVATE--DEFAULT--A PRIVATE DBLINK IS ONLY AVAILABLE TO THE USER WHO HAS CREATED IT.IT IS NOT POSSIBLE FOR A USER TO GRANT ACCESS ON A PRIVATE DBLINK TO OTHER USERS.
-------
PUBLIC--IT IS AVAILABLE TO ALL THE USERS.
-------
SHARED--SHARED DATABASE LINK USES SHARED SERVER CONNECTION TO SUPPORT DATABASE LINK CONNECTION. IF THERE ARE MULTIPLE CONCURRENT DATABASE LINK ACCESS INTO A REMOTE DATABASE, SHARED DATABASE LINK CAN BE USED TO REDUCE THE NO OF SERVER CONNECTIONS REQUIRED. WITHOUT THE SHARED CLAUSE EACH DATABASE LINK CONNECTION REQUIRES A SEPARATE CONNECTION TO THE REMOTE DATABASE.

In dblink we can use 2 types of login.
-------------------------------------------------------
1.DEFAULT LOGIN----USER NAME AND PASWORD IS SAME IN BOTH THE DATABASES.

Syntax
-------------
CREATE [PUBLIC] DATABASE LINK <DB.N.> CONNECT TO CURRENT_USER USING<'CONNECTI ON STRING'>

create public database link daslink connect to current_user  using 'ORCL' 

2.EXPLICIT LOGIN-----USER NAME AND PASWORD IS DIFFERENT IN BOTH THE DATABASES.

Syntax
-------------
CREATE [PUBLIC|SHARED] DATABASE LINK <DBLINK NAME> CONNECT TO<USERNAME>IDE NTIFIED BY<PASSWORD> USING<'CONNECTI ON STRING'>

CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'ORCL'
 

Note:---TO CREATE PUBLIC DBLINK THE USER MUST HAVE CREATE PUBLIC DATABASE LINK SYSTEM PREVILEDGE.