Oracle Session Undo Usage Report
Displays undo usage information for active database sessions involved in transactions. Useful for monitoring undo consumption and identifying sessions generating high undo activity.
oraclesqlperformance-tuningv1.0.0
0 stars0 downloads4 views0 comments
By OracleDba • Created
Code
(28 lines)12345678910111213141516171819202122232425262728
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/session_undo.sql
-- Author : Tim Hall
-- Description : Displays undo information on relevant database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @session_undo
-- Last Modified: 29/03/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;