V$TRANSACTION - Monitor Active Transactions & Undo Usage

V$TRANSACTION

Overview

V$TRANSACTION displays one row for every active transaction in the Oracle instance. DBAs use this view to identify long-running transactions, measure undo space consumption, find uncommitted work that may be blocking others, and understand redo generation patterns. It is the primary starting point for any transaction-level investigation.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$TRANSACTION or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
ADDR	RAW(8)	Address of the transaction state object in the SGA
XIDUSN	NUMBER	Undo segment number assigned to this transaction
XIDSLOT	NUMBER	Slot number within the undo segment header
XIDSQN	NUMBER	Sequence number; combined with XIDUSN and XIDSLOT forms the transaction ID (XID)
UBAFIL	NUMBER	Undo block address — file number
UBABLK	NUMBER	Undo block address — block number
UBASQN	NUMBER	Undo block address — sequence number
UBAREC	NUMBER	Undo block address — record number
STATUS	VARCHAR2(16)	Transaction status: ACTIVE, COLLECTING, PREPARED, etc.
START_TIME	VARCHAR2(20)	Wall-clock time when the transaction started (MM/DD/YY HH24:MI:SS)
START_SCNB	NUMBER	SCN at which the transaction started (lower 32 bits)
START_SCNW	NUMBER	SCN wrap value at transaction start
USED_UBLK	NUMBER	Number of undo blocks currently used by this transaction
USED_UREC	NUMBER	Number of undo records currently used by this transaction
LOG_IO	NUMBER	Logical I/O count since transaction start
PHY_IO	NUMBER	Physical I/O count since transaction start
CR_GET	NUMBER	Consistent-read block gets performed by this transaction
CR_CHANGE	NUMBER	Number of consistent-read changes applied
START_DATE	DATE	Date and time the transaction began (Oracle 10g+)
DSCN_B	NUMBER	Dependent SCN — used for distributed transactions
SES_ADDR	RAW(8)	Address of the session owning this transaction; joins to V$SESSION.SADDR
FLAG	NUMBER	Bitmask of internal transaction flags
SPACE	VARCHAR2(3)	YES if this is a space transaction
RECURSIVE	VARCHAR2(3)	YES if this is a recursive transaction
NOUNDO	VARCHAR2(3)	YES if no undo is being generated (e.g., NOLOGGING DML)
PTX	VARCHAR2(3)	YES if this is a parallel transaction

Essential Queries

Basic Usage

List all active non-recursive transactions with their age and undo consumption:

SELECT
    t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN   AS xid,
    t.STATUS,
    t.START_TIME,
    ROUND((SYSDATE - TO_DATE(t.START_TIME, 'MM/DD/YY HH24:MI:SS')) * 1440, 1)
                                                          AS age_minutes,
    t.USED_UBLK                                           AS undo_blocks,
    t.USED_UREC                                           AS undo_records,
    t.LOG_IO                                              AS logical_ios,
    t.PHY_IO                                              AS physical_ios
FROM
    V$TRANSACTION t
WHERE
    t.RECURSIVE = 'NO'
ORDER BY
    age_minutes DESC;

Monitoring Query

Join V$TRANSACTION with V$SESSION to identify which user, machine, and SQL statement owns each transaction:

SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.MACHINE,
    s.PROGRAM,
    s.STATUS                                              AS session_status,
    t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN     AS xid,
    t.START_TIME,
    ROUND((SYSDATE - TO_DATE(t.START_TIME, 'MM/DD/YY HH24:MI:SS')) * 1440, 2)
                                                          AS age_minutes,
    t.USED_UBLK                                           AS undo_blocks,
    t.USED_UREC                                           AS undo_records,
    t.LOG_IO                                              AS logical_ios,
    t.PHY_IO                                              AS physical_ios,
    sq.SQL_TEXT                                           AS current_sql
FROM
    V$TRANSACTION  t
    JOIN V$SESSION  s  ON s.SADDR = t.SES_ADDR
    LEFT JOIN V$SQL sq ON sq.SQL_ID = s.SQL_ID
WHERE
    t.RECURSIVE = 'NO'
ORDER BY
    age_minutes DESC;

Combined with Other Views

Identify undo segment pressure by correlating transaction undo usage with undo tablespace free space:

SELECT
    u.SEGMENT_NAME,
    u.STATUS                                              AS seg_status,
    COUNT(t.XIDUSN)                                       AS active_txns,
    SUM(t.USED_UBLK)                                      AS total_undo_blks,
    SUM(t.USED_UREC)                                      AS total_undo_recs,
    MAX(ROUND((SYSDATE - TO_DATE(t.START_TIME,
               'MM/DD/YY HH24:MI:SS')) * 1440, 1))        AS oldest_txn_min
FROM
    V$TRANSACTION  t
    JOIN V$ROLLSTAT r  ON r.USN = t.XIDUSN
    JOIN DBA_ROLLBACK_SEGS u ON u.SEGMENT_ID = t.XIDUSN
GROUP BY
    u.SEGMENT_NAME,
    u.STATUS
ORDER BY
    total_undo_blks DESC;

Advanced Analysis

Find transactions generating the most redo — useful for identifying heavy DML workloads or runaway batch jobs:

SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.MACHINE,
    s.MODULE,
    s.ACTION,
    t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN     AS xid,
    t.START_TIME,
    ROUND((SYSDATE - TO_DATE(t.START_TIME, 'MM/DD/YY HH24:MI:SS')) * 60, 1)
                                                          AS age_seconds,
    t.USED_UBLK                                           AS undo_blocks,
    t.USED_UREC                                           AS undo_records,
    t.LOG_IO                                              AS logical_ios,
    t.PHY_IO                                              AS physical_ios,
    -- Estimate redo size from undo consumption (rough heuristic: undo ~ 30-50% of redo)
    ROUND(t.USED_UBLK * 8192 / 1024 / 1024, 2)           AS est_undo_mb,
    st.VALUE                                              AS session_redo_bytes
FROM
    V$TRANSACTION  t
    JOIN V$SESSION      s  ON s.SADDR    = t.SES_ADDR
    LEFT JOIN V$SESSTAT st ON st.SID     = s.SID
                          AND st.STATISTIC# = (
                              SELECT STATISTIC#
                              FROM   V$STATNAME
                              WHERE  NAME = 'redo size'
                          )
WHERE
    t.RECURSIVE = 'NO'
ORDER BY
    st.VALUE DESC NULLS LAST;

Find all sessions with uncommitted transactions that have been idle for more than 5 minutes — a common blocking pattern:

SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.STATUS                                              AS session_status,
    s.LAST_CALL_ET                                        AS idle_seconds,
    s.MACHINE,
    s.PROGRAM,
    t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN     AS xid,
    t.START_TIME,
    ROUND((SYSDATE - TO_DATE(t.START_TIME, 'MM/DD/YY HH24:MI:SS')) * 1440, 2)
                                                          AS txn_age_minutes,
    t.USED_UBLK                                           AS undo_blocks,
    t.USED_UREC                                           AS undo_records
FROM
    V$TRANSACTION  t
    JOIN V$SESSION  s ON s.SADDR = t.SES_ADDR
WHERE
    t.RECURSIVE  = 'NO'
    AND s.STATUS  = 'INACTIVE'
    AND s.LAST_CALL_ET > 300   -- idle more than 5 minutes
ORDER BY
    txn_age_minutes DESC;

Common Use Cases

• Long-running transaction detection — Alert on transactions older than a threshold to catch forgotten BEGIN/no-COMMIT patterns before they balloon undo usage.
• Undo space capacity planning — SUM(USED_UBLK) across all active transactions gives the floor of undo space that must remain available at any instant.
• Blocking lock analysis — V$TRANSACTION.SES_ADDR ties directly into V$SESSION, enabling full blocking-waiter chain reconstruction when combined with V$LOCK.
• Distributed transaction monitoring — Transactions with STATUS = ‘COLLECTING’ or ‘PREPARED’ indicate in-flight two-phase commit operations that may need DBA intervention if they stall.
• Rollback time estimation — A long-running transaction that has been killed can be tracked via V$TRANSACTION while it rolls back; USED_UBLK decreases over time.
• Application health checks — Idle sessions holding open transactions (INACTIVE status + V$TRANSACTION row) typically indicate application-side connection pooling or error-handling defects.

Related Views

• V$SESSION — Joins via SES_ADDR = SADDR; provides user, machine, program, and current SQL for the transaction owner.
• V$LOCK — Rows with TYPE = ‘TX’ represent transaction locks; ADDR in V$LOCK matches ADDR in V$TRANSACTION.
• V$ROLLSTAT — Undo segment statistics; joins on USN = XIDUSN to see per-segment activity.
• V$UNDOSTAT — 10-minute interval snapshots of undo usage and undo retention; complements point-in-time data in V$TRANSACTION.
• DBA_ROLLBACK_SEGS — Provides human-readable undo segment names correlated by SEGMENT_ID = XIDUSN.

Version Notes

• Oracle 9i: START_DATE column added as a proper DATE type alongside the VARCHAR2 START_TIME column.
• Oracle 10g: NOUNDO column added to flag NOLOGGING or direct-path operations that bypass undo generation.
• Oracle 11g: Automatic Undo Management (AUM) became the only supported mode; XIDUSN values are now always system-managed undo segments.
• Oracle 12c (Multitenant): In a CDB, V$TRANSACTION is container-scoped; query CDB$ROOT as SYSDBA and use CDB_TRANSACTION (not a standard view — use V$TRANSACTION from each PDB) to see all PDB transactions.
• Oracle 19c / 21c: No structural changes to V$TRANSACTION; flashback data archive (FDA/Total Recall) rows may appear with RECURSIVE = ‘YES’.
• Oracle 23ai: Blockchain table DML generates normal transaction entries; no schema change to V$TRANSACTION itself.