DB Link in Oracle SQL

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’;

Example

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

CREATE DATABASE LINK dev2fut CONNECT TO apps IDENTIFIED BY apps123 USING
'(DESCRIPTION = 
    (ADDRESS=(PROTOCOL=tcp)(HOST=fut.oracleappsdna.com)(PORT=1521)) 
      (CONNECT_DATA= 
        (SERVICE_NAME=oadna-fut) 
          (INSTANCE_NAME=OADNA-FUT)))';

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