DBA_DB_LINKS - Query All Database Links in Oracle

DBA_DB_LINKS

Overview

DBA_DB_LINKS lists every database link defined in the database, regardless of owner. A database link is a schema object that allows a local session to query or manipulate objects in a remote Oracle database as though they were local, using the @link_name suffix. DBAs use this view to audit the database link landscape, identify stale or broken links, enforce naming conventions, and investigate distributed transaction problems (ORA-02063, ORA-01591).

The view exposes only the link definition (owner, name, remote username, and TNS connect string), not the remote password — Oracle stores that encrypted in SYS.LINK$. To test whether a link is actually functional, a session must execute SELECT 1 FROM DUAL@link_name, which triggers a real network connection to the remote host.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_DB_LINKS requires SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY; users can query ALL_DB_LINKS (links they can use) or USER_DB_LINKS (links they own) without additional privileges

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the database link; PUBLIC for public links accessible by all users
DB_LINK	VARCHAR2(128)	Database link name as specified in CREATE DATABASE LINK; typically matches the service name or tnsnames alias
USERNAME	VARCHAR2(128)	Remote Oracle username the link connects as; NULL for links that connect as the current user (connect through authentication)
HOST	VARCHAR2(2000)	TNS connect descriptor or tnsnames alias for the remote database; may be a full EZConnect string or a name requiring tnsnames.ora resolution
CREATED	DATE	Timestamp when the database link was created

Essential Queries

Basic Usage

List all database links with their owner, remote user, and remote host:

SELECT
    l.owner,
    l.db_link,
    l.username                                  AS remote_user,
    l.host                                      AS connect_string,
    l.created,
    CASE l.owner
        WHEN 'PUBLIC' THEN 'Public (all users)'
        ELSE 'Private (owner only)'
    END                                         AS link_scope
FROM
    dba_db_links l
ORDER BY
    l.owner,
    l.db_link;

DBA_DB_LINKS vs ALL_DB_LINKS vs USER_DB_LINKS

Show all three views side by side to understand what each user perspective exposes — useful for documenting access:

-- DBA view: all links in the database (requires DBA or SELECT_CATALOG_ROLE)
SELECT 'DBA_DB_LINKS' AS view_name, owner, db_link, username, host
FROM   dba_db_links
UNION ALL
-- All view: links the current user can use (public + owned by this user)
SELECT 'ALL_DB_LINKS', owner, db_link, username, host
FROM   all_db_links
UNION ALL
-- User view: links owned by the current user only
SELECT 'USER_DB_LINKS', USER, db_link, username, host
FROM   user_db_links
ORDER BY 1, 2, 3;

Find Links by Remote Host or Service

Search for all links pointing to a specific remote host or service name — essential when a remote database is being decommissioned or migrated:

SELECT
    l.owner,
    l.db_link,
    l.username                                  AS remote_user,
    l.host                                      AS connect_string,
    l.created
FROM
    dba_db_links l
WHERE
    UPPER(l.host) LIKE UPPER('%&remote_host_or_service%')
    OR UPPER(l.db_link) LIKE UPPER('%&remote_host_or_service%')
ORDER BY
    l.owner,
    l.db_link;

Audit Old or Potentially Stale Links

Find database links created more than a specified number of days ago, which may be stale if the remote database has been decommissioned:

SELECT
    l.owner,
    l.db_link,
    l.username                                  AS remote_user,
    l.host,
    l.created,
    TRUNC(SYSDATE - l.created)                  AS days_old,
    CASE
        WHEN l.created < ADD_MONTHS(SYSDATE, -24) THEN 'Over 2 years old'
        WHEN l.created < ADD_MONTHS(SYSDATE, -12) THEN 'Over 1 year old'
        WHEN l.created < ADD_MONTHS(SYSDATE, -6)  THEN 'Over 6 months old'
        ELSE 'Recent'
    END                                         AS age_category
FROM
    dba_db_links l
ORDER BY
    l.created ASC;

Find Active DB Link Sessions in V$DBLINK

Cross-reference DBA_DB_LINKS with V$DBLINK to see which defined links currently have open sessions:

SELECT
    dl.owner,
    dl.db_link,
    dl.username                                 AS remote_user,
    dl.host,
    vl.logged_on,
    vl.heterogeneous,
    vl.open_cursors,
    vl.in_transaction,
    vl.update_sent
FROM
    dba_db_links    dl
    LEFT JOIN v$dblink vl ON UPPER(vl.db_link) = UPPER(dl.db_link)
ORDER BY
    vl.logged_on DESC NULLS LAST,
    dl.owner,
    dl.db_link;

Generate DROP DATABASE LINK Statements

Produce DDL to drop all private links for a specific schema — useful during schema cleanup or user decommissioning:

SELECT
    'DROP ' ||
    CASE WHEN owner = 'PUBLIC' THEN 'PUBLIC ' ELSE '' END ||
    'DATABASE LINK ' ||
    CASE WHEN owner != 'PUBLIC' THEN owner || '.' ELSE '' END ||
    '"' || db_link || '";'                      AS drop_statement
FROM
    dba_db_links
WHERE
    owner = UPPER('&schema_owner')   -- replace with target schema or PUBLIC
ORDER BY
    owner,
    db_link;

Common Use Cases

• Pre-decommission impact analysis — Before retiring a remote database, query DBA_DB_LINKS filtering on HOST to find all links pointing to it; then check V$DBLINK and V$SESSION for active usage, and DBA_DEPENDENCIES for objects (views, procedures, packages) that reference those links
• Broken link identification — After a remote database is renamed, re-homed, or its listener configuration changes, links with old connect strings fail with ORA-12154 or ORA-02063; export the HOST column to compare against the current tnsnames.ora entries
• Security audit of link credentials — Links that specify a hardcoded USERNAME (fixed remote user) are more brittle than connect-through links (USERNAME = NULL); document and flag any links connecting as a privileged remote account such as SYS or SYSTEM
• Public vs private link governance — Public database links (OWNER = ‘PUBLIC’) are accessible to every schema in the database; they are convenient but introduce risk if the remote credentials are compromised; audit policy may require all links to be private
• Distributed transaction investigation — ORA-01591 (lock held by in-doubt distributed transaction) and ORA-02063 (preceding line from dblink) errors are traced back to the DB link name in the error text; query DBA_DB_LINKS to find the link definition and then V$DBLINK for in_transaction = ‘Y’ entries to identify the problematic session
• Migration and clone validation — After cloning a production database to a test environment, PUBLIC database links may still point to production remote databases; audit DBA_DB_LINKS immediately after a clone and drop or recreate links pointing to inappropriate targets

Related Views

• V$DBLINK — Shows currently open database link sessions; join on DB_LINK name to correlate active connections with link definitions in DBA_DB_LINKS
• V$SESSION — Active sessions using a database link show the remote database name in the SERVER column; join to identify which user is using which link
• DBA_DEPENDENCIES — Lists objects (views, procedures, triggers) that reference a database link by name; query before dropping a link to assess the impact
• V$DBLINK — Active link sessions; always check before dropping a link to avoid interrupting an active distributed transaction
• ALL_DB_LINKS — The non-DBA version; accessible without special privileges and shows links the current user can use
• USER_DB_LINKS — Links owned by the current schema; useful for application developers to verify their own link configuration

Version Notes

• Oracle 7: DBA_DB_LINKS introduced with OWNER, DB_LINK, USERNAME, HOST, and CREATED columns
• Oracle 8i: HETEROGENEOUS column added to some views to support non-Oracle (HS/Gateway) remote databases
• Oracle 10g: USERNAME column expanded to 30 characters; links can reference Oracle Database Vault-protected schemas
• Oracle 12c Multitenant: In a CDB, DBA_DB_LINKS queried from the CDB root shows links across all open PDBs when COMMON = ‘YES’; each PDB’s DBA_DB_LINKS shows only that PDB’s links; CON_ID column added to distinguish source container
• Oracle 19c: No structural changes; SYS.LINK$ encryption improved; the HOST column may contain EZConnect Plus syntax (//host:port/service?transport.connect_timeout=5)
• Oracle 21c / 23ai: No structural changes; Oracle Database Link authentication can use Oracle Wallet–stored credentials; the USERNAME column may be NULL for wallet-authenticated links