V$DBLINK - Monitor Active Database Link Sessions

V$DBLINK

Overview

V$DBLINK shows every database link that is currently open within the calling session. Unlike DBA_DB_LINKS, which is a persistent data dictionary view listing all defined links, V$DBLINK is a dynamic performance view that only shows links with an active connection at the time of the query. A row appears here the first time a statement references a link (e.g., SELECT * FROM table@linkname) and disappears once the session closes or the link is explicitly closed with a commit or rollback that resolves all distributed activity.

DBAs use V$DBLINK to detect in-doubt distributed transactions (IN_TRANSACTION = ‘Y’ combined with UPDATE_SENT = ‘Y’), to confirm that a link is genuinely connected before attempting to drop it, and to measure open cursor consumption over remote connections. It is a natural companion to DBA_DB_LINKS and is particularly important when diagnosing ORA-01591 (lock held by in-doubt distributed transaction) or ORA-02063 (preceding line from dblink).

View Type: Dynamic Performance View (session-specific) Available Since: Oracle 7 Required Privileges: SELECT on V_$DBLINK or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY; shows all open links across all sessions with these privileges

Key Columns

Column	Datatype	Description
DB_LINK	VARCHAR2(128)	Name of the database link as defined in DBA_DB_LINKS
OWNER_ID	NUMBER	USER# (user identifier) of the schema that owns the link; join to DBA_USERS.USER_ID
LOGGED_ON	VARCHAR2(3)	YES if the link currently has an authenticated connection to the remote database; NO if defined but not yet connected
HETEROGENEOUS	VARCHAR2(3)	YES if the link connects to a non-Oracle data source via Heterogeneous Services (HS) or a Database Gateway
PROTOCOL	VARCHAR2(6)	Communication protocol used for the link connection (e.g., TCP)
OPEN_CURSORS	NUMBER	Number of cursors currently open on the remote database through this link; high values may indicate cursor leaks
IN_TRANSACTION	VARCHAR2(3)	YES if a distributed transaction is in progress over this link; the link cannot be closed until the transaction completes
UPDATE_SENT	VARCHAR2(3)	YES if DML (INSERT, UPDATE, DELETE) has been sent to the remote database in the current transaction; indicates a two-phase commit will be required
COMMIT_POINT_STRENGTH	NUMBER	Relative commit point strength used by Oracle to elect the commit point site in a two-phase commit; higher value means this instance is more likely to be chosen as the coordinator

Essential Queries

Basic Usage

Show all currently open database link connections for all sessions:

SELECT
    v.db_link,
    u.username                                  AS link_owner,
    v.logged_on,
    v.heterogeneous,
    v.protocol,
    v.open_cursors,
    v.in_transaction,
    v.update_sent,
    v.commit_point_strength
FROM
    v$dblink        v
    LEFT JOIN dba_users u ON u.user_id = v.owner_id
ORDER BY
    v.in_transaction DESC,
    v.update_sent    DESC,
    v.db_link;

In-Doubt Distributed Transaction Detection

Find links that have sent DML and are in a transaction — these are at risk of becoming in-doubt if the remote database or network fails:

SELECT
    v.db_link,
    u.username                                  AS link_owner,
    d.host                                      AS remote_host,
    d.username                                  AS remote_user,
    v.logged_on,
    v.in_transaction,
    v.update_sent,
    v.open_cursors,
    v.commit_point_strength,
    CASE
        WHEN v.in_transaction = 'YES' AND v.update_sent = 'YES'
            THEN 'Two-phase commit required — DO NOT drop link'
        WHEN v.in_transaction = 'YES' AND v.update_sent = 'NO'
            THEN 'Read-only distributed transaction in progress'
        ELSE 'No active distributed transaction'
    END                                         AS transaction_status
FROM
    v$dblink        v
    LEFT JOIN dba_users     u ON u.user_id        = v.owner_id
    LEFT JOIN dba_db_links  d ON UPPER(d.db_link) = UPPER(v.db_link)
                             AND (d.owner = u.username OR d.owner = 'PUBLIC')
WHERE
    v.in_transaction = 'YES'
ORDER BY
    v.update_sent DESC,
    v.db_link;

Open Cursor Leak Detection over DB Links

Find links with an unusually high number of open remote cursors — a sign that the application is not closing cursors after distributed queries:

SELECT
    v.db_link,
    u.username                                  AS link_owner,
    d.host                                      AS remote_host,
    d.username                                  AS remote_user,
    v.open_cursors,
    v.logged_on,
    v.in_transaction,
    CASE
        WHEN v.open_cursors > 100 THEN 'CRITICAL - possible cursor leak'
        WHEN v.open_cursors > 50  THEN 'WARNING  - investigate cursor usage'
        ELSE 'Normal'
    END                                         AS cursor_alert
FROM
    v$dblink        v
    LEFT JOIN dba_users     u ON u.user_id        = v.owner_id
    LEFT JOIN dba_db_links  d ON UPPER(d.db_link) = UPPER(v.db_link)
                             AND (d.owner = u.username OR d.owner = 'PUBLIC')
ORDER BY
    v.open_cursors DESC;

Full Session Context for Active DB Link Connections

Join V$DBLINK with DBA_DB_LINKS and V$SESSION to understand which sessions are using which links and what they are currently doing:

SELECT
    v.db_link,
    u.username                                  AS link_owner,
    d.host                                      AS remote_connect_string,
    d.username                                  AS remote_user,
    v.logged_on,
    v.in_transaction,
    v.update_sent,
    v.open_cursors,
    s.sid,
    s.serial#,
    s.username                                  AS local_user,
    s.status,
    s.event,
    s.machine,
    s.program,
    s.module,
    SUBSTR(q.sql_text, 1, 150)                  AS current_sql
FROM
    v$dblink        v
    LEFT JOIN dba_users     u ON u.user_id        = v.owner_id
    LEFT JOIN dba_db_links  d ON UPPER(d.db_link) = UPPER(v.db_link)
                             AND (d.owner = u.username OR d.owner = 'PUBLIC')
    LEFT JOIN v$session      s ON s.username = u.username
                             AND s.status    = 'ACTIVE'
    LEFT JOIN v$sql          q ON q.sql_id = s.sql_id
ORDER BY
    v.in_transaction DESC,
    v.open_cursors   DESC;

Heterogeneous Services Link Audit

Identify non-Oracle linked connections (HS Gateway links to SQL Server, MySQL, etc.) which have different performance and transaction characteristics:

SELECT
    v.db_link,
    u.username                                  AS link_owner,
    d.host                                      AS remote_connect_string,
    d.username                                  AS remote_user,
    v.heterogeneous,
    v.protocol,
    v.logged_on,
    v.in_transaction,
    v.open_cursors,
    d.created                                   AS link_created
FROM
    v$dblink        v
    LEFT JOIN dba_users     u ON u.user_id        = v.owner_id
    LEFT JOIN dba_db_links  d ON UPPER(d.db_link) = UPPER(v.db_link)
                             AND (d.owner = u.username OR d.owner = 'PUBLIC')
WHERE
    v.heterogeneous = 'YES'
ORDER BY
    v.db_link;

Common Use Cases

In-doubt distributed transaction diagnosis — When ORA-01591 fires, query V$DBLINK filtering on IN_TRANSACTION = ‘YES’ AND UPDATE_SENT = ‘YES’ to find the link involved; the link’s DB_LINK name appears in the error message and can be matched here; then query DBA_2PC_PENDING to find the pending transaction and decide whether to force commit or force rollback
Pre-drop link safety check — Before executing DROP DATABASE LINK, confirm the link is not listed in V$DBLINK with IN_TRANSACTION = ‘YES’; dropping a link mid-transaction causes ORA-02080 and may leave a distributed transaction in doubt
Cursor leak monitoring — Applications using database links without properly closing cursors accumulate OPEN_CURSORS entries; a script that checks V$DBLINK for high open_cursors values and alerts when they exceed a threshold (e.g., 50 per link) catches connection pool issues before they escalate to ORA-01000
Network latency investigation — A link with LOGGED_ON = ‘YES’ but high wait times visible in V$SESSION (event containing ‘db link’) indicates latency on the link’s underlying network path; use OPEN_CURSORS and timing from V$SESSION to quantify the impact
Post-clone link audit — After cloning a production database to test, V$DBLINK should show no active connections for links pointing to production systems; if it does, application connection pools have already reached out to production through the clone’s copied links
Two-phase commit coordinator identification — COMMIT_POINT_STRENGTH determines which database is elected as the commit point site; documenting this value per link helps explain distributed transaction commit behaviour when coordinating between multiple Oracle databases with different COMMIT_POINT_STRENGTH init.ora settings

DBA_DB_LINKS — Persistent link definitions; V$DBLINK shows which of those defined links currently have an active connection
V$SESSION — Join to correlate active link connections with the sessions using them and their current wait events
DBA_2PC_PENDING — Shows in-doubt distributed transactions; the TRAN_COMMENT and DB_TRAN columns identify which database link was involved; used alongside V$DBLINK when resolving ORA-01591
DBA_2PC_NEIGHBORS — Lists the participants in each pending distributed transaction; provides the remote database identity for each in-doubt transaction found in DBA_2PC_PENDING
V$GLOBAL_TRANSACTION — Shows all active global (distributed) transactions; join with V$DBLINK on the session context to see which links are involved in each global transaction

Version Notes

Oracle 7: V$DBLINK introduced with DB_LINK, LOGGED_ON, HETEROGENEOUS, PROTOCOL, OPEN_CURSORS, IN_TRANSACTION, UPDATE_SENT, and COMMIT_POINT_STRENGTH columns
Oracle 8i: OWNER_ID column added to identify the link owner’s user number; previously only the session context implied ownership
Oracle 10g: No structural changes; Streams-based replication and LogMiner capture may hold DB links open for extended periods, visible here
Oracle 12c Multitenant: CON_ID column added; in a CDB, V$DBLINK queried from the CDB root shows all active link connections across all open PDBs; each PDB sees only its own link sessions
Oracle 19c: No structural changes; DRCP (Database Resident Connection Pooling) connections using database links now correctly maintain V$DBLINK state across pool borrows
Oracle 21c / 23ai: No structural column changes; Blockchain Table cross-database queries may open links with HETEROGENEOUS = ‘NO’ even when the remote database version differs; COMMIT_POINT_STRENGTH semantics unchanged