Create DB link

Oracle DB Link Creation

What is a DB link?

 Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Pre-requisites to create a DB link:

 CREATE DATABASE LINK : This privilege is is required to create a private db link

 CREATE PUBLIC DATABASE LINK: This privilege is required to create a Public db link – which all users can access. 

  CREATE SESSION PRIVILEGE : --required on the remote oracle database

SHARED: - Specify SHARED  to shared the db link with multiple sessions

When you specify SHARED you must also specify the dblink_authentication clause for the users

authorized to use the database link.

The examples that follow assume two databases, one with the database name local and the other with

the database name remote. The examples use the Oracle Database domain. Your database domain will

be different.

The following statement defines a shared public database link named remote that refers to the database

specified by the service name remote:

CREATE PUBLIC DATABASE LINK remote USING 'remote';

This database link allows user hr on the local database to update a table on the remote database

(assuming hr has appropriate privileges):

UPDATE employees@remote

SET salary=salary*1.1

WHERE last_name = 'Baer';

In the following statement, user hr on the remote database defines a fixed-user database link named loca

to the hr schema on the local database:

CREATE DATABASE LINK local

CONNECT TO hr IDENTIFIED BY password USING 'local';

After this database link is created, hr can query tables in the schema hr on the local database in this

manner:

SELECT * FROM employees@local;