Query to find Undo usage with SID & Username in Oracle

 

Query to find Undo usage with SID & Username in Oracle

     
SELECT s.username,
	s.sid,
	s.serial#,
	t.used_ublk "Number of undo blocks used",
	t.used_urec "Number of undo records used",
	rs.segment_name,
	r.rssize,
	r.status,
  'alter system kill session '''||s.sid||','||s.serial# ||''''||';'
   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;