Database Link Basics

A database link is simply a connection from one database instance (let’s call it local database) to another database instance (let’s call it remote database) to allow transparently executing SQL statements against the remote database in the context of the local database. The application client connects to the local database and can then access objects of the remote database as if they were objects in the local database (almost).

Oracle provides two different types of database links: Private and Public. Private database links are only accessible to a specific database user, while public database links are available to all database users. For the remaining article, only private database links will be used (for security reasons, this is the preferred approach in any case). In addition, there is also the concept of a global database link where remote databases are published in a directory server – please refer to Oracle documentation for further information.

To create a database link, the CREATE DATABASE LINK statement is used. There are some variants on the format, especially regarding user authentication – please refer to the ORACLE documentation for more information. In general, to create a link to a remote database, the following syntax applies:

CREATE DATABASE LINK LinkName
CONNECT TO RemoteUser IDENTIFIED BY Password
USING 'ConnectString';

After creating the database link, any remote tables in the remote user’s schema can be accessed by appending @LinkName:

SELECT *
FROM TableName@LinkName;

To get rid of a database link, we can use the DROP DATABASE LINK statement:

DROP DATABASE LINK LinkName;

SELECT * 
FROM TableName@LinkName;

ERROR at line 1: ORA-02019: connection description for remote database not found

To retrieve all database links which are available to the current user, use a query like

SELECT * 
FROM ALL_DB_LINKS;

This lists all public database links and the database links owned by the current user.