Oracle DATABASE 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.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites

• To create a private database link, you must have the CREATE DATABASE LINK system privilege.
• To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege.
• You must have the CREATE SESSION system privilege on the remote Oracle database.
• You must be able to connect to the remote database.
• Oracle Net must be installed on both the local and remote Oracle databases.

Database Link Types

When you create a database link, you must decide who will have access to it. The following sections describe how to create the basic types of links.

Private Database Links

CREATE DATABASE LINK mylink
USING 'tns_service_name';

Above private link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE DATABASE LINK mylink
CONNECT TO remote_username IDENTIFIED BY mypassword 
USING 'tns_service_name';

Above private fixed user link to the database with service name 'tns_service_name'. The link connects to the remote database with the remote_username / mypassword regardless of the connected user.

Public Database Links

CREATE PUBLIC DATABASE LINK mylink
USING 'tns_service_name';

Above public link to the remote database. The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE PUBLIC DATABASE LINK mylink
CONNECT TO remote_username IDENTIFIED BY mypassword 
USING 'tns_service_name';

Above public fixed user link. The link connects to the remote database with the remote_username / mypassword .

How to Use

Once Database Link is created you can access remote database objects by using @dblink.

Examples

SELECT * FROM employees@mylink;

UPDATE employees@mylink
   SET salary=salary*1.1
   WHERE last_name = 'Baer';

You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:

CREATE SYNONYM emp_table 
   FOR oe.employees@mylink;

SELECT * FROM emp_table;

Drop Database Link

Use the DROP DATABASE LINK statement to remove a database link from the database.

A private database link must be in your own schema.

DROP DATABASE LINK mylink;

To drop a PUBLIC database link, you must have the DROP PUBLIC DATABASE LINK system privilege.

DROP PUBLIC DATABASE LINK mylink;