How to Use Oracle Database Links for Remote Database Connections

on October 30, 2015

Database links tell Oracle how to get from one database to another. If you frequently use the same connection to a remote database, a database link is appropriate.

How a Database Link Works

A database link requires Oracle Net Services (previously known as SQL*Net and Net8) to be running on each of the machines (hosts) involved in the remote database access. Oracle Net Services is usually started by the database administrator (DBA) or the system manager. Sample architecture for remote access using a database link is shown in Figure 1. This figure shows two hosts, each running Oracle Net Services. There is a database on each of the hosts. A database link defines a connection from the first database (named LOCAL, on the Branch host) to the second database (named REMOTE, on the Headquarters host). The database link shown in Figure 1 is located in the LOCAL database.


FIGURE 1. Sample architecture for database link

To support communication between the two databases, the Oracle Net Services configuration must include listener processes for the databases and service names for the databases. Oracle Net Services configuration files include tnsnames.ora (for translating database service names to databases, hosts, and ports) and listener.ora (for specifying the connection information for databases on the local host).

Database links specify the following connection information:

  •  The communications protocol (such as TCP/IP) to use during the connection.
  •  The host on which the remote database resides.
  •  The name of the database on the remote host.
  • The name of a valid account in the remote database (optional).
  •  The password for that account (optional).

When used, a database link actually logs in as a user in the remote database and the database link remains open for your session until you either log out of your session or execute the ALTER SESSION CLOSE DATABASE LINK command. A database link can be private, owned by a single user, or public, in which case all users in the LOCAL database can use the link.

Related Posts

Leave a Reply