Checking rollback/Undo segment info used by SID (single – multiple)


column
rr heading ‘RB Segment’ format a18
column
us heading ‘Username’ format a15
column
os heading ‘OS User’ format a10
column
te heading ‘Terminal’ format a10
SELECT
r.name rr, nvl(s.username,’no transaction’) us,s.sid, s.osuser os, s.terminal te,
rs.rssize,    
rs.xacts,
rs.rssize/1048576 Rssize
FROM
v$lock  l, v$session  s,v$rollname 
r , v$rollstat rs
WHERE
l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = ‘TX’ AND
l.lmode
= 6   AND r.usn=rs.usn  and s.sid in (&sid_list_comma_sep);
  • April 24, 2012 | 45 views
  • Comments