How to Check Active Transactions in Oracle Database
Monitor active transactions in Oracle Database with optimized SQL queries. Track undo usage, transaction duration, and rollback segments for DBAs.
Active transactions represent uncommitted work in your Oracle database and are critical to monitor for database health and performance. Long-running or stuck transactions can consume excessive undo space, cause blocking issues, and impact overall database performance. As an Oracle DBA, regularly checking active transactions helps you identify problematic queries, detect application issues, prevent undo tablespace failures, and maintain optimal database operations.
This guide provides production-ready SQL scripts to monitor active transactions with detailed metrics including transaction duration, undo consumption, rollback segment usage, and I/O statistics. Use these queries for daily health checks, troubleshooting performance issues, or investigating blocking scenarios.
-- ====================================================================
-- Script: active_transactions_monitor.sql
-- Purpose: Monitor active transactions with lock and rollback details
-- Usage: Identifies uncommitted transactions and their impact
-- ====================================================================
SET LINESIZE 300
SET PAGESIZE 100
SET COLSEP '|'
SET VERIFY OFF
SET FEEDBACK OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
-- Column Formatting
COLUMN inst_id FORMAT 9999 HEADING "INST|ID"
COLUMN sid FORMAT 99999 HEADING "SID"
COLUMN serial# FORMAT 99999 HEADING "SERIAL#"
COLUMN os_pid FORMAT A10 HEADING "OS|PID"
COLUMN username FORMAT A15 HEADING "DB USERNAME" TRUNCATE
COLUMN status FORMAT A8 HEADING "STATUS"
COLUMN osuser FORMAT A12 HEADING "OS USER" TRUNCATE
COLUMN machine FORMAT A20 HEADING "CLIENT MACHINE" TRUNCATE
COLUMN program FORMAT A25 HEADING "PROGRAM" TRUNCATE
COLUMN sql_id FORMAT A13 HEADING "SQL_ID"
COLUMN xid FORMAT A18 HEADING "TRANSACTION_ID"
COLUMN start_time FORMAT A16 HEADING "TXN START TIME"
COLUMN used_ublk FORMAT 999999 HEADING "UNDO|BLOCKS"
COLUMN used_urec FORMAT 999999 HEADING "UNDO|RECORDS"
COLUMN log_io FORMAT 999999 HEADING "LOG|IO"
COLUMN phy_io FORMAT 999999 HEADING "PHY|IO"
COLUMN txn_duration FORMAT 9999.99 HEADING "TXN|MIN"
COLUMN rollback_segment FORMAT A10 HEADING "RBS|NAME" TRUNCATE
PROMPT
PROMPT ========================================================================
PROMPT ACTIVE TRANSACTIONS MONITORING REPORT
PROMPT ========================================================================
PROMPT
SELECT
s.inst_id,
s.sid,
s.serial#,
p.spid AS os_pid,
s.username,
s.status,
s.osuser,
s.machine,
s.program,
s.sql_id,
t.xidusn || '.' || t.xidslot || '.' || t.xidsqn AS xid,
TO_CHAR(t.start_date, 'DD-MON-YY HH24:MI') AS start_time,
ROUND((SYSDATE - t.start_date) * 24 * 60, 2) AS txn_duration,
t.used_ublk,
t.used_urec,
t.log_io,
t.phy_io,
r.name AS rollback_segment
FROM
gv$session s
JOIN gv$process p ON s.paddr = p.addr AND s.inst_id = p.inst_id
JOIN gv$transaction t ON s.saddr = t.ses_addr AND s.inst_id = t.inst_id
LEFT JOIN v$rollname r ON t.xidusn = r.usn
WHERE
s.username IS NOT NULL
ORDER BY
s.inst_id,
txn_duration DESC,
s.username;
PROMPT
PROMPT ========================================================================
PROMPT TRANSACTION SUMMARY BY INSTANCE
PROMPT ========================================================================
PROMPT
SELECT
inst_id AS "INST_ID",
COUNT(*) AS "ACTIVE_TRANSACTIONS",
ROUND(AVG((SYSDATE - start_date) * 24 * 60), 2) AS "AVG_TXN_MIN",
ROUND(MAX((SYSDATE - start_date) * 24 * 60), 2) AS "MAX_TXN_MIN",
SUM(used_ublk) AS "TOTAL_UNDO_BLOCKS",
SUM(used_urec) AS "TOTAL_UNDO_RECORDS"
FROM
gv$transaction
GROUP BY
inst_id
ORDER BY
inst_id;
PROMPT
PROMPT ========================================================================
PROMPT LONG RUNNING TRANSACTIONS (>5 MINUTES)
PROMPT ========================================================================
PROMPT
SELECT
s.inst_id,
s.sid,
s.serial#,
s.username,
s.program,
ROUND((SYSDATE - t.start_date) * 24 * 60, 2) AS "TXN_DURATION_MIN",
t.used_ublk AS "UNDO_BLOCKS",
s.sql_id,
TO_CHAR(t.start_date, 'DD-MON-YY HH24:MI:SS') AS "START_TIME"
FROM
gv$session s
JOIN gv$transaction t ON s.saddr = t.ses_addr AND s.inst_id = t.inst_id
WHERE
(SYSDATE - t.start_date) * 24 * 60 > 5
ORDER BY
(SYSDATE - t.start_date) DESC;
PROMPT
PROMPT ========================================================================
PROMPT ROLLBACK SEGMENT USAGE
PROMPT ========================================================================
PROMPT
SELECT
r.name AS "ROLLBACK_SEGMENT",
COUNT(t.xidusn) AS "ACTIVE_TRANSACTIONS",
SUM(t.used_ublk) AS "TOTAL_UNDO_BLOCKS",
SUM(t.used_urec) AS "TOTAL_UNDO_RECORDS"
FROM
v$rollname r
LEFT JOIN gv$transaction t ON r.usn = t.xidusn
GROUP BY
r.name
HAVING
COUNT(t.xidusn) > 0
ORDER BY
COUNT(t.xidusn) DESC;
PROMPT
PROMPT ========================================================================
SET FEEDBACK ONSample Output
========================================================================
ACTIVE TRANSACTIONS MONITORING REPORT
========================================================================
INST| SID|SERIAL#|OS |DB USERNAME |STATUS |OS USER |CLIENT MACHINE |PROGRAM |SQL_ID |TRANSACTION_ID |TXN START TIME | TXN| UNDO| UNDO| LOG| PHY|RBS |
ID| | |PID | | | | | | | | | MIN|BLOCKS|RECORDS| IO| IO|NAME |
----|-----|-------|----------|----------------|--------|-------------|---------------------|--------------------------|--------------|-------------------|-----------------|-------|------|-------|------|------|----------|
1| 342| 23456|27834 |APPUSER |ACTIVE |oracle |app-server-01 |JDBC Thin Client |8xyk2m3np4q7s|5.23.1045 |10-FEB-26 09:15 | 25.50| 12450| 45623| 15234| 8934|_SYSSMU5$ |
1| 156| 45678|28912 |BATCHUSER |ACTIVE |batch |batch-server-01 |python@batch-server-01 |k7zt3n8mp2q5w|3.45.2301 |10-FEB-26 09:25 | 15.25| 8934| 32145| 9823| 5621|_SYSSMU3$ |
1| 478| 12389|29045 |WEBUSER |ACTIVE |webadmin |web-server-02 |Apache Tomcat |f3np8q2mk5z7y|7.12.3456 |10-FEB-26 09:35 | 5.10| 1234| 5678| 2341| 1245|_SYSSMU7$ |
2| 234| 34567|30123 |RPTUSER |ACTIVE |reportuser |rpt-server-01 |sqlplus@rpt-server-01 |a9mz5k2lp8w3x|2.34.4567 |10-FEB-26 09:30 | 10.75| 5623| 18934| 6234| 3421|_SYSSMU2$ |
========================================================================
TRANSACTION SUMMARY BY INSTANCE
========================================================================
INST_ID ACTIVE_TRANSACTIONS AVG_TXN_MIN MAX_TXN_MIN TOTAL_UNDO_BLOCKS TOTAL_UNDO_RECORDS
------- ------------------- ----------- ----------- ----------------- ------------------
1 3 15.28 25.50 22618 83446
2 1 10.75 10.75 5623 18934
========================================================================
LONG RUNNING TRANSACTIONS (>5 MINUTES)
========================================================================
INST_ID SID SERIAL# DB USERNAME PROGRAM TXN_DURATION_MIN UNDO_BLOCKS SQL_ID START_TIME
------- ----- ------- --------------- ------------------------- ---------------- ----------- ------------- -------------------
1 342 23456 APPUSER JDBC Thin Client 25.50 12450 8xyk2m3np4q7s 10-FEB-26 09:15:23
1 156 45678 BATCHUSER python@batch-server-01 15.25 8934 k7zt3n8mp2q5w 10-FEB-26 09:25:12
2 234 34567 RPTUSER sqlplus@rpt-server-01 10.75 5623 a9mz5k2lp8w3x 10-FEB-26 09:30:45
1 478 12389 WEBUSER Apache Tomcat 5.10 1234 f3np8q2mk5z7y 10-FEB-26 09:35:18
========================================================================
ROLLBACK SEGMENT USAGE
========================================================================
ROLLBACK_SEGMENT ACTIVE_TRANSACTIONS TOTAL_UNDO_BLOCKS TOTAL_UNDO_RECORDS
---------------- ------------------- ----------------- ------------------
_SYSSMU5$ 1 12450 45623
_SYSSMU3$ 1 8934 32145
_SYSSMU7$ 1 1234 5678
_SYSSMU2$ 1 5623 18934Notes for DBAs
Query Features
- Transaction Identification: Shows unique transaction ID (XID) for tracking
- Duration Tracking: Calculates how long each transaction has been running
- Resource Usage: Displays undo blocks, undo records, and I/O statistics
- Rollback Segment Info: Shows which rollback segment is being used
- RAC Compatible: Works across all instances in a RAC environment
- Long Transaction Detection: Separate section for transactions running >5 minutes
Key Columns Explained
Transaction Metrics:
- TRANSACTION_ID (XID): Format is
UNDO_SEGMENT.SLOT.SEQUENCE– unique identifier - TXN MIN: Duration the transaction has been running (in minutes)
- UNDO BLOCKS: Number of undo blocks consumed (indicates transaction size)
- UNDO RECORDS: Number of undo records generated
- LOG IO: Redo log I/O operations
- PHY IO: Physical I/O operations
- RBS NAME: Rollback segment (undo segment) being used
Common Use Cases
1. Find transactions by specific user:
AND UPPER(s.username) = 'APPUSER'2. Find transactions running longer than N minutes:
AND (SYSDATE - t.start_date) * 24 * 60 > 30 -- More than 30 minutes3. Find large transactions (high undo usage):
AND t.used_ublk > 10000 -- Using more than 10,000 undo blocks4. Find transactions on specific machine:
AND UPPER(s.machine) LIKE '%APP-SERVER%'
Was this helpful?
Thanks for your feedback!