Database Links — From Service Name to Remote Database Connectivity

In Lesson 7, service names were established as the foundation of Oracle distributed network topology — a unique identifier that Oracle Net translates into the protocol, host address, port, and database service information needed to reach a remote Oracle database. A service name alone, however, is not sufficient to establish a distributed database connection from within SQL. The next step is to create a database link — a schema object that takes a service name and adds the remote user identity and authentication credentials needed to open a session on the remote database. Together, the service name and database link form the complete connectivity path for distributed Oracle queries and cross-database transactions.

What Is a Database Link?

A database link is a pointer stored in one Oracle database that defines a connection to another Oracle database. Once a DBA creates a database link, any authorized user or application can reference objects on the remote database as if they were local — using the @link_name syntax in SQL statements. The remote databases can participate in queries, DML operations, and distributed transactions from within any Oracle application connected to the local database.

The original Oracle 8/Net8 example that established this lesson's context creates two public database links connecting a Denver database to systems in London and Paris:

-- Oracle 8 / Net8 era syntax (still parses in Oracle 23ai)
CREATE PUBLIC DATABASE LINK london
  CONNECT TO 'user_id' IDENTIFIED BY 'secret_password'
  USING 'london_unix';

CREATE PUBLIC DATABASE LINK paris
  CONNECT TO 'user_id' IDENTIFIED BY 'secret_password'
  USING 'paris_vms';

The USING clause specifies the TNS service name — london_unix and paris_vms in this example. Oracle Net looks up these service names in the tnsnames.ora file to obtain the protocol, IP address, port number, and database service identifier needed to establish the network connection. The CONNECT TO clause provides the remote user ID and password that Oracle uses to authenticate the session on the remote database once the network connection is established.

The USING Clause — TNS Service Name Resolution

The USING clause is technically optional in CREATE DATABASE LINK, but in practice it must be supplied unless a public database link to the destination database using the desired connect string already exists. The value supplied to USING can be:

• A TNS alias — a short name resolved through tnsnames.ora or a directory naming service. This is the traditional approach:

  USING 'london_tns'

• An Easy Connect Plus string (Oracle 23ai recommended) — the full connection inline without a tnsnames.ora entry:

  USING 'tcps://london-scan:2484/sales_pdb?ssl_server_dn_match=yes'

• A full TNS connect descriptor — the complete connection description embedded directly in the USING clause without requiring a tnsnames.ora lookup.

In Oracle 23ai, Easy Connect Plus is the recommended naming method for database links in OCI environments — it eliminates the tnsnames.ora dependency and works with OCI's Centralized Configuration Providers.

How Database Links Are Resolved

A database can have multiple database links with the same name — several users may each have a private link to the same remote database, and a public database link to that remote site may also exist. When a user references a remote object, Oracle applies a resolution algorithm to determine which database link to use and how to authenticate the connection:

1. Oracle first looks for a private database link owned by the current user with the specified link name. If found, that link's connect string and credentials are used.
2. If no private link exists, Oracle looks for a public database link with the specified link name. If found, that link's connect string is used.
3. Oracle resolves the TNS service name in the USING clause through the configured naming method — tnsnames.ora lookup, Easy Connect Plus parsing, LDAP directory query, or Centralized Configuration Provider.
4. Oracle establishes the network connection to the remote database using Oracle Net over the resolved protocol, host, and port.
5. Oracle authenticates the session on the remote database using the credentials specified in the database link's CONNECT TO clause.

Oracle does not necessarily obtain the connect string and authentication credentials from a single database link. In Current User links (the modern recommended approach), the connect string comes from the link definition while the authentication credentials come from the currently logged-in user's session — the two pieces of information are sourced independently.

Evolution from Oracle 8/Net8 to Oracle 23ai

The core CREATE DATABASE LINK syntax has remained essentially unchanged from Oracle 8 through Oracle 23ai. The London and Paris examples from the original lesson still parse and execute correctly in 23ai. What has changed substantially is the security model, the network encryption defaults, and the recommended authentication approach.

Modern Secure Database Link Creation in Oracle 19c and 23ai

Oracle strongly recommends moving away from fixed-user public links with hardcoded passwords. Three approaches are available, ranked from most to least secure:

Option 1 — Current User Link (Recommended, No Password Stored)

The preferred method when the connecting user has a matching account on the remote database. No password is ever stored in the database link definition or the data dictionary:

-- Private Current User link — Oracle 19c / 23ai recommended approach
CREATE DATABASE LINK london
  CONNECT TO CURRENT_USER
  USING 'london_tns';

CREATE DATABASE LINK paris
  CONNECT TO CURRENT_USER
  USING 'paris_tns';

The link uses the credentials of the currently logged-in user to authenticate on the remote database. This works well in multitenant CDB/PDB environments and with Oracle Enterprise Users where a single enterprise identity maps to accounts on multiple databases.

Option 2 — Fixed User Link with Secure External Password Store (Wallet)

When Current User links are not possible, store credentials in an OS-protected Oracle Wallet rather than in the SQL statement. The password never appears in the database link definition or in any SQL script:

-- Step 1: Create the wallet (run once on the database server OS)
mkstore -wrl /path/to/wallet -create

-- Step 2: Add credentials for each remote database
mkstore -wrl /path/to/wallet -createCredential london_tns remote_user "VeryStrongPassw0rd!2026"
mkstore -wrl /path/to/wallet -createCredential paris_tns  remote_user "VeryStrongPassw0rd!2026"

Configure sqlnet.ora to point to the wallet:

WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /path/to/wallet)))
SQLNET.WALLET_OVERRIDE = TRUE

Create the database link with no password in the SQL:

-- Wallet-based database link — password stored in OS wallet, not in SQL
CREATE DATABASE LINK london
  CONNECT TO remote_user
  USING 'london_tns';

CREATE DATABASE LINK paris
  CONNECT TO remote_user
  USING 'paris_tns';

Option 3 — Fixed User Link with Strong Password (Acceptable, Not Preferred)

When neither Current User nor wallet-based authentication is available, a private fixed-user link with a strong password is acceptable — but avoid PUBLIC scope:

-- Private Fixed-User link — use only when Options 1 and 2 are not available
CREATE DATABASE LINK london
  CONNECT TO remote_user IDENTIFIED BY "VeryStrongPassw0rd!2026"
  USING 'london_tns';

Before creating a fixed-user link after an upgrade, verify that the remote account uses the 12C password version — the 10G version is desupported in Oracle 23ai:

-- Run on the remote database to verify password version
SELECT username, password_versions
FROM   dba_users
WHERE  username = 'REMOTE_USER';

-- If 10G version appears, reset the password on the remote database
ALTER USER remote_user IDENTIFIED BY "VeryStrongPassw0rd!2026";

Network Security Hardening for Database Links

Database link traffic traverses the network between two Oracle databases using the same Oracle Net protocol as client-to-server connections. In Oracle 23ai, TLS 1.3 is the default for all Oracle Net connections including database links. For environments where explicit encryption configuration is required, add the following to sqlnet.ora on both the local and remote database servers:

-- sqlnet.ora — configure on both local and remote database servers
SQLNET.ENCRYPTION_SERVER         = REQUIRED
SQLNET.ENCRYPTION_CLIENT         = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER   = (AES256)
SQLNET.ENCRYPTION_TYPES_CLIENT   = (AES256)
SQLNET.CRYPTO_CHECKSUM_SERVER    = REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT    = REQUIRED

Setting REQUIRED on both sides ensures that unencrypted connections are rejected — a database link attempting to connect without encryption will fail rather than fall back to unencrypted communication.

Database Link Security — Quick Reference

Summary

A database link completes the distributed connectivity path that a service name begins — combining the network address resolution provided by the TNS service name with the remote user identity needed to authenticate a session on the remote database. The core CREATE DATABASE LINK syntax has not changed from Oracle 8 through Oracle 23ai, but the security model has changed substantially: public fixed-user links with hardcoded passwords were standard practice in the Net8 era and are now actively discouraged. Current User links, wallet-based Secure External Password Store, and private link scope are the Oracle 19c and 23ai recommended approaches. Network encryption through AES256 or TLS 1.3 applies to database link traffic and must be configured on both the local and remote database servers. The next lesson examines how database links are used in practice — joining tables from remote sites in a single SQL query.