Oracle Database Link

In distributed Oracle environments, database links can be used to define a communications path between two databases. A database link defines a network connection, and, optionally, a username and password, to use when Oracle accesses and establishes a database session in the remote database on behalf of the local application request.

Database link enable SQL statements to be isolated from the underlying physical network topology. Thus, whenever the location of a remote database changes, only the link need be updated, and not each and every SQL queries, significantly the tasks of programmer.

Oracle database link is defined in a specific database, and may be used only by users of that database. If the same database link name was to be used from all databases in the network, the links must be defined identically with that name in each of each databases. For this scenario, the easier way is to be global database link.

Database Link Creation

Login into Oracle database (with SQL*Plus, for example) as a user who has the privilege to create a database link and execute the following command:

CREATE DATABASE LINK link_name
     CONNECT TO user_name IDENTIFIED BY password 
     USING 'connection_string';

where connection_string is an entry is tnsnames.ora (usually located in $ORACLE_HOME/network/admin), which enables the Oracle to know the network path to remote database.

connection_string has the following structure:

(DESCRIPTION = 
     (ADDRESS = 
          (PROTOCOL = TCP) (Host = host_name/ip_address) (Port = 1521)
     )
     (CONNECT_DATA = 
          (SERVER = DEDICATED)
          (SERVICE_NAME = database_name)
     )
)

Database Link Deletion

To delete a database link:

DROP DATBASE LINK link_name;