Script to find Session using Undo

By Ari kaplan


set pagesize 24 select to_char(rownum+3) ||') ' || rpad(r.name,17) || rpad(to_char(p.pid),11) || rpad(p.spid,11) || rpad(nvl(p.username,'NO TRANSACTION'),17) || rpad(p.terminal,8) FROM v$lock l, v$process p, v$rollname r, v$session s WHERE l.sid = s.sid (+) and p.addr = s.paddr and trunc(l.id1(+)/65536)=r.usn and l.type(+) = 'TX' and l.lmode(+) = 6 UNION select '2) ROLLBACK SEGMENT'||' '|| 'ORACLE PID' ||' '|| 'SYSTEM PID' ||' '|| 'TRANSACTION ' ||' '|| 'TERMINAL' from dual UNION select '1) ' from dual UNION select '3) ----------------' ||' '|| '----------' ||' '|| '----------' ||' '|| '----------------' ||' '|| '--------' from dual ORDER BY 1 /