Oracle Database Links - Create, Manage & Troubleshoot

Oracle Database Links - Create, Manage & Troubleshoot

Database links allow one Oracle database to query and manipulate data in another Oracle database (or a non-Oracle database via a gateway) as if the remote objects were local. They are widely used for distributed queries, data replication, migration, and cross-system reporting. This guide covers link creation, authentication types, monitoring open links, closing stale connections, common errors, and performance considerations.

How Database Links Work

When a session references TABLE_NAME@LINK_NAME, Oracle’s SQL engine:

1. Looks up the link definition in SYS.LINK$ (exposed via DBA_DB_LINKS).
2. Opens a connection to the remote database using the link’s connect string.
3. Authenticates using the link’s credentials.
4. Forwards the SQL to the remote instance.
5. Streams results back across the network.

The remote connection persists for the session’s lifetime unless explicitly closed, which means long-running sessions accumulate connections on the remote side.

---

Creating Database Links

Private Database Link

Private links are visible only to the creating user.

-- Fixed user link: always connects as a specific remote user
CREATE DATABASE LINK sales_db
  CONNECT TO reporting_user IDENTIFIED BY "str0ngP@ss"
  USING 'sales_prod';

-- Test the link
SELECT sysdate FROM dual@sales_db;

-- Query a remote table
SELECT customer_id, order_total
FROM orders@sales_db
WHERE order_date > SYSDATE - 7;

Public Database Link

Public links are accessible to all database users. Require the CREATE PUBLIC DATABASE LINK privilege.

-- Public link visible to all users
CREATE PUBLIC DATABASE LINK finance_db
  CONNECT TO finance_reader IDENTIFIED BY "r3@derPass"
  USING 'finance_prod';

-- Drop a public link (requires DROP PUBLIC DATABASE LINK privilege)
DROP PUBLIC DATABASE LINK finance_db;

Connected User Link (No Credentials)

A connected user link does not store credentials. Oracle uses the calling session’s username and attempts to authenticate with the same password on the remote database. Rarely used in modern systems.

-- Connected user link - remote DB must have matching user/password
CREATE DATABASE LINK hr_dev
  USING 'hr_dev_db';
-- No CONNECT TO clause = connected user semantics

Current User Link

Current user links authenticate using Oracle’s global user mechanism (requires Oracle Advanced Security or centralized directory). The link connects as the current session user without storing credentials.

-- Current user link (requires enterprise user security setup)
CREATE DATABASE LINK global_link
  CONNECT TO CURRENT_USER
  USING 'remote_db';

Shared Database Links

Shared links allow multiple sessions to reuse a single connection to the remote database. They require the DBMS_SESSION package and a shared server (MTS) configuration.

-- Shared link reduces remote connection count significantly
CREATE SHARED DATABASE LINK reporting_shared
  AUTHENTICATED BY reporting_auth IDENTIFIED BY "authP@ss"
  CONNECT TO reporting_user IDENTIFIED BY "r3portPass"
  USING 'reporting_db';

---

TNS Connect Strings

The USING clause accepts either a TNS alias (resolved via tnsnames.ora or LDAP) or a full connect descriptor inline.

-- Using a TNS alias (preferred - centrally managed)
CREATE DATABASE LINK mylink CONNECT TO user1 IDENTIFIED BY pass1
  USING 'MY_TNS_ALIAS';

-- Using a full inline connect descriptor (useful when tnsnames.ora is unavailable)
CREATE DATABASE LINK mylink_inline
  CONNECT TO user1 IDENTIFIED BY pass1
  USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=db-server.company.com)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=ORCL)))';

---

GLOBAL_NAMES Impact

When GLOBAL_NAMES = TRUE (the default in many environments), the database link name must match the global name of the target database exactly. This enforces naming discipline but can cause ORA-02085 if the names do not match.

-- Check GLOBAL_NAMES setting
SHOW PARAMETER global_names;

-- Check the remote database's global name
SELECT global_name FROM global_name@your_link;

-- Check the local setting
SELECT global_name FROM global_name;

-- Temporarily disable for a session (for testing only)
ALTER SESSION SET global_names = FALSE;

-- Rename the link to match the remote global name
-- (requires dropping and recreating the link)
DROP DATABASE LINK old_name_link;
CREATE DATABASE LINK remote_db.company.com
  CONNECT TO user1 IDENTIFIED BY pass1
  USING 'remote_tns';

---

Testing and Using Database Links

-- Basic connectivity test
SELECT sysdate FROM dual@sales_db;

-- Return the remote database name and version
SELECT name, db_unique_name, version FROM v$database@sales_db;

-- Query remote table with WHERE clause pushed to remote (good)
SELECT order_id, amount
FROM orders@sales_db
WHERE customer_id = 12345;

-- Distributed join (data transfer across link)
SELECT l.customer_name, r.order_total
FROM customers l
JOIN orders@sales_db r
  ON l.customer_id = r.customer_id
WHERE l.region = 'APAC';

-- Insert into a remote table
INSERT INTO audit_log@central_db (event_time, event_type, details)
VALUES (SYSDATE, 'BATCH_COMPLETE', 'Processed 5000 rows');
COMMIT;

-- Use synonyms to hide the link name from application code
CREATE SYNONYM remote_orders FOR orders@sales_db;
SELECT * FROM remote_orders WHERE rownum <= 10;

---

Monitoring Open Database Links

V$DBLINK

V$DBLINK shows all database links opened in the current session.

-- Links open in the current session
SELECT
  db_link,
  owner_id,
  logged_on,
  heterogeneous,
  protocol,
  open_cursors,
  in_transaction,
  update_sent,
  commit_point_strength
FROM v$dblink;

DBA_DB_LINKS

-- All database links in the database (requires DBA privilege)
SELECT
  owner,
  db_link,
  username,
  host,
  TO_CHAR(created, 'DD-MON-YYYY') AS created
FROM dba_db_links
ORDER BY owner, db_link;

-- Public database links
SELECT db_link, username, host, created
FROM dba_db_links
WHERE owner = 'PUBLIC'
ORDER BY db_link;

-- Links with no stored credentials (connected user links)
SELECT owner, db_link, host
FROM dba_db_links
WHERE username IS NULL
ORDER BY owner, db_link;

Sessions Using Database Links

-- Find all sessions currently using a database link (via open cursors or V$SESSION)
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  s.status,
  s.sql_id,
  TO_CHAR(s.logon_time, 'DD-MON HH24:MI') AS logon_time
FROM v$session s
WHERE s.sid IN (
  SELECT sid FROM v$open_cursor
  WHERE sql_text LIKE '%@%'
);

-- Remote sessions opened by local sessions (approximate)
-- Best viewed from the remote database side:
-- SELECT machine, program, logon_time FROM v$session WHERE program LIKE '%Oracle%';

---

Closing Open Database Links

An open database link holds a connection on the remote database. Stale open links are a common cause of “maximum sessions exceeded” errors on remote databases.

-- Close a specific database link in the current session
ALTER SESSION CLOSE DATABASE LINK sales_db;

-- Verify it is closed
SELECT db_link, logged_on FROM v$dblink;

-- There is no DDL to close links in OTHER sessions.
-- To release remote connections from other sessions, kill the local session:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

---

Common Errors and Fixes

ORA-02063: Preceding Line from Link Name

This error means the remote database returned an error. The local error is a wrapper; the root cause is on the remote side.

-- The error usually includes the original error code on the preceding line
-- Example:
-- ORA-00942: table or view does not exist
-- ORA-02063: preceding line from SALES_DB

-- Debug by testing the link connectivity first:
SELECT sysdate FROM dual@sales_db;

-- Then check if the remote object exists with the remote user's privileges:
SELECT table_name FROM tables@sales_db WHERE table_name = 'ORDERS';

ORA-02068: Following Severe Error from Link Name

-- ORA-02068 indicates the remote database raised a severe or fatal error.
-- Common causes: remote DB restarted, network interruption, remote session killed.

-- Close and reopen the link:
ALTER SESSION CLOSE DATABASE LINK affected_link;
SELECT sysdate FROM dual@affected_link;  -- Forces reconnect

ORA-02019: Connection Description for Remote Database Not Found

-- The TNS alias in the USING clause cannot be resolved.
-- Check tnsnames.ora, LDAP, or use a full descriptor.

-- Verify the TNS alias resolves:
-- From OS: tnsping SALES_PROD

-- Check the sqlnet.ora resolution order:
-- NAMES.DIRECTORY_PATH = (TNSNAMES, LDAP, EZCONNECT)

-- View link definition to confirm the USING string:
SELECT db_link, host FROM dba_db_links WHERE db_link = 'SALES_DB';

ORA-01017 via a Database Link

-- The stored credentials in the link are wrong or the password has changed.
-- Must drop and recreate the link with correct credentials.

DROP DATABASE LINK sales_db;
CREATE DATABASE LINK sales_db
  CONNECT TO reporting_user IDENTIFIED BY "new_password"
  USING 'sales_prod';

ORA-02085: Database Link Name Connects to a Different Database

-- The link name does not match the remote database's GLOBAL_NAMES value.
-- Either disable GLOBAL_NAMES or rename the link.

-- Check remote global name:
SELECT global_name FROM global_name@your_link;

-- Recreate the link with the correct name:
DROP DATABASE LINK old_link_name;
CREATE DATABASE LINK remote_db.domain.com
  CONNECT TO user1 IDENTIFIED BY pass1
  USING 'remote_tns';

---

Password Management for Database Links

Link passwords are stored encrypted in SYS.LINK$. When the remote user’s password changes, the link breaks with ORA-01017 until it is recreated.

-- Identify links that may have stale passwords (links created a long time ago)
SELECT
  owner,
  db_link,
  username,
  host,
  created,
  ROUND(SYSDATE - created) AS age_days
FROM dba_db_links
WHERE created < SYSDATE - 180  -- Links older than 180 days
ORDER BY created;

-- Script to recreate a link (note: password must be retrieved from a password store)
-- DDL cannot extract the stored password from LINK$ directly.
-- Best practice: store link passwords in a secure vault (Oracle Wallet, HashiCorp Vault).

-- Using Oracle Wallet to avoid storing plaintext passwords in DDL:
-- Configure sqlnet.ora with WALLET_LOCATION, then:
CREATE DATABASE LINK secure_link
  CONNECT TO reporting_user IDENTIFIED BY EXTERNAL 'cn=reporting_user,dc=company,dc=com'
  USING 'target_db';

---

Heterogeneous Connectivity (Oracle Gateway)

Oracle Database Gateway allows database links to connect to non-Oracle databases (SQL Server, MySQL, Sybase, etc.) using Oracle’s transparent gateway or generic connectivity.

-- Heterogeneous link to SQL Server via Oracle Gateway
-- Requires Oracle Gateway installation and listener configuration

CREATE DATABASE LINK sqlserver_link
  CONNECT TO "sa" IDENTIFIED BY "SqlServerPass"
  USING 'sqlserver_gateway_tns';

-- Query SQL Server table
SELECT * FROM "dbo"."customers"@sqlserver_link WHERE rownum <= 10;

-- Check if a link is heterogeneous
SELECT db_link, heterogeneous FROM v$dblink;

---

Performance Considerations

Driving Site Hint

By default, Oracle executes a distributed query on the local instance and fetches data from the remote database. On large joins between a small local table and a large remote table, it is often faster to execute on the remote side.

-- Default: local site drives, fetches remote data
SELECT l.id, r.amount
FROM local_customers l
JOIN remote_orders@sales_db r ON l.id = r.customer_id;

-- DRIVING_SITE hint: push execution to the remote site
SELECT /*+ DRIVING_SITE(r) */ l.id, r.amount
FROM local_customers l
JOIN remote_orders@sales_db r ON l.id = r.customer_id;

Reducing Round Trips

-- Bad: fetches entire large table, filters locally
SELECT * FROM big_remote_table@link WHERE status = 'ACTIVE';

-- Better: push the filter to remote (Oracle usually does this automatically)
-- but confirm with execution plan that the predicate is pushed

-- Check distributed query execution plan
EXPLAIN PLAN FOR
  SELECT * FROM orders@sales_db WHERE order_date > SYSDATE - 7;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for REMOTE step - predicates listed under it are pushed to remote

-- Avoid SELECT * across a link - specify only needed columns
SELECT order_id, customer_id, order_total
FROM orders@sales_db
WHERE order_date > SYSDATE - 7;

Bulk Operations Over Links

-- Batch inserts over a link are slower than direct path loads.
-- For large data movements, prefer Data Pump or RMAN over database links.

-- For moderate volumes, use array processing in PL/SQL:
DECLARE
  TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  l_ids t_ids;
  l_limit CONSTANT PLS_INTEGER := 1000;

  CURSOR c IS
    SELECT order_id FROM orders@sales_db WHERE processed = 'N';
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_ids LIMIT l_limit;
    EXIT WHEN l_ids.COUNT = 0;

    FORALL i IN 1 .. l_ids.COUNT
      INSERT INTO local_staging (order_id, load_time)
      VALUES (l_ids(i), SYSDATE);

    COMMIT;
  END LOOP;
  CLOSE c;
END;
/

---

Security Best Practices

1. Prefer fixed user links with minimal privileges - The remote user should have only SELECT (or the specific DML operations needed) on the required objects.
2. Avoid PUBLIC database links with stored credentials - Any database user can use a public link. Prefer private links scoped to the application schema.
3. Rotate link passwords on the same schedule as the remote account - Coordinate the link recreation with the password rotation to avoid outages.
4. Audit database link usage - Enable auditing on CREATE DATABASE LINK and ALTER SESSION CLOSE DATABASE LINK.
5. Use Oracle Wallet instead of inline passwords - Wallet-based credentials eliminate plaintext passwords from DDL history and SYS.LINK$.
6. Review DBA_DB_LINKS regularly - Remove links that are no longer needed.

-- Audit database link creation and use
AUDIT CREATE DATABASE LINK BY ACCESS;
AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS;

-- View recent database link audit records
SELECT
  db_user,
  action_name,
  obj_name,
  TO_CHAR(extended_timestamp, 'DD-MON-YY HH24:MI:SS') AS action_time,
  returncode
FROM dba_audit_trail
WHERE action_name IN ('CREATE DATABASE LINK', 'DROP DATABASE LINK')
ORDER BY extended_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

---

Related Topics

• Oracle Error ORA-02063 - Preceding line from database link
• Oracle Net Configuration - TNS and listener setup for link connectivity
• Oracle Data Pump - Alternative to database links for large data transfers
• Security Analysis Scripts - Privilege and access auditing queries