Thursday, May 22, 2008

Oracle Database Links (DB Link)

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

Type of DB Links

There are 3 types of DB links. They are as follows:
1. PRIVATE: When the DB links is created, it is created under Private mode as default. The 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.

2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.

3. SHARED: Shared database link uses share the 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 number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.

Types of Logins:

In dblink we can use 2 types of login. They are as follows:
1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
-------------
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING<'CONNECTION STRING'>

Code: (Text)
Create public database link daslink connect to current_user using 'ORCL'

2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
-------------
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING<'CONNECTION STRING'>

Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'ORCL'

Note: To create the public DBLINK the user must have create public database link system privileges.

No comments: