DB Link in Oracle SQL

By | December 7, 2014 | 453 views | Category: Oracle Apps Basics Oracle SQL Tags:

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.

The following link types are supported:

  1. Private database link – belongs to a specific schema of a database. Only the owner of a private database link can use it.
  2. Public database link – all users in the database can use it.
  3. Global database link – defined in an OID or Oracle Names Server. Anyone on the network can use it.

Syntax to create DB Link

CREATE DATABASE LINK remotedb CONNECT TO &user_name IDENTIFIED BY &password USING ‘tns_conn_str’;


Here is an example to connect FUT instance from DEV instance. DB Link should be created in DEV to access FUT from DEV.


Usage of DB Link

Below is an example to access EMP table in FUT instance from DEV instance using above created DB link

SELECT * FROM emp@dev2fut;

Below DB Views are helpful for working with DB links in Oracle:

  1. DBA_DB_LINKS – All DB links defined in the database
  2. ALL_DB_LINKS – All DB links the current user has access to
  3. USER_DB_LINKS – All DB links owned by current user
WOW! Did you like this post? We'll send more interesting posts like DB Link in Oracle SQL to you!
Enter your Email Address: