How to Create Database Links in Oracle

Data sometimes lives in another Oracle database: a reporting copy, a partner system, an older application. A database link lets SQL in one database reach tables in another as table@link, in queries, joins, and DML, without exporting and loading data in between.

Code for This Guide

The main examples are in the examples/schema-objects folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [public] database link [if not exists] link
  connect to user identified by password
  using 'connect_string'

select ... from table@link

The link stores the remote user and password, encrypted, and a connect string such as host:port/service. A private link belongs to its owner; a PUBLIC link is usable by every user and needs a privilege to create.

A Link to the Same Database

The example creates a link back to the same database, LOOPBACK, which is enough to show how links work. Replace &nimbus_password with the password of your NIMBUS user; in SQLcl, the ampersand makes it a substitution variable that prompts for the value.

Example:

create database link loopback
  connect to nimbus identified by "&nimbus_password"
  using 'localhost:1521/FREEPDB1';

select count(*) as airports_over_the_link from airports@loopback;

select db_link, username, host from user_db_links;

Output:

Database link LOOPBACK created.

   AIRPORTS_OVER_THE_LINK
_________________________
                       22

DB_LINK     USERNAME    HOST
___________ ___________ __________________________
LOOPBACK    NIMBUS      localhost:1521/FREEPDB1

The query counts the airports through the link, and USER_DB_LINKS shows the link's user and connect string, but not the password. The example drops the link afterward.

Join Local and Remote Tables

A remote table joins a local one like any other table.

Example:

create database link loopback2
  connect to nimbus identified by "&nimbus_password"
  using 'localhost:1521/FREEPDB1';

-- a remote table joins a local one like any other
select r.origin, a.city, count(*) as routes
from   routes r join airports@loopback2 a on a.airport_code = r.origin
where  r.origin in ('SIN', 'SYD')
group  by r.origin, a.city
order  by r.origin;

Output:

Database link LOOPBACK2 created.

ORIGIN    CITY            ROUTES
_________ ____________ _________
SIN       Singapore            3
SYD       Sydney               3

The routes come from the local table and the cities through the link. Oracle sends the remote part of the query to the other database and combines the results.

Things to Know

• A synonym such as create synonym remote_airports for airports@link hides the link from the code that uses it.
• DML through a link takes part in the local transaction, and a commit commits both databases together.
• Large remote joins can be slow, because rows travel over the network; filter on the remote side, and consider the DRIVING_SITE hint or a materialized view.
• The remote user's password is stored in the link: limit who can create and use links, and prefer private links.

Related Guides

• How to Create Materialized Views in Oracle

Conclusion

A database link connects SQL to another Oracle database, so remote tables can be queried, joined, and changed as table@link. Store the remote credentials only where needed, hide links behind synonyms, and filter on the remote side for large data.