12 Undo Active Transactions

oracle_dba_perf_toolkit/daily_dba/12_undo_active_transactions.sql

Disclaimer: This script is provided for educational and learning purposes. Review it carefully, test in a non-production environment, confirm privileges, and validate impact before using it in production.

SET PAGESIZE 200
SET LINESIZE 280
SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK ON
SET TAB OFF

COLUMN session_id FORMAT A20
COLUMN username FORMAT A20
COLUMN status FORMAT A10
COLUMN sql_id FORMAT A13
COLUMN used_ublk FORMAT 999,999,999
COLUMN used_urec FORMAT 999,999,999
COLUMN start_time FORMAT A22

SELECT
    s.inst_id || ':' || s.sid || ',' || s.serial# session_id,
    s.username,
    s.status,
    s.sql_id,
    t.used_ublk,
    t.used_urec,
    t.start_time
FROM
    gv$transaction t
    JOIN gv$session s
      ON s.inst_id = t.inst_id
     AND s.taddr = t.addr
ORDER BY
    t.used_ublk DESC;