Rollback Segments

Here are two scripts to help monitor the rollback segments:

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)
|| rpad(s.sid,4)
|| rpad(s.serial#,8)
|| rpad(s.module,15)
|| rpad(s.command,15)
|| rpad(s.username,15)
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 ‘
|| ‘SID ‘
|| ‘Serial# ‘
|| ‘Module          ‘
|| ‘Command         ‘
|| ‘Username        ‘
from dual
UNION
select ‘1) ‘ from dual
UNION
select ‘3) —————- ‘
||’———- ‘
||’———- ‘
||’—————- ‘
||’——– ‘
||’— ‘
||’——- ‘
||’————– ‘
||’————– ‘
||’————– ‘
from dual
ORDER BY 1
/

Using this select statement you can monitor the Rollback segment usage in Oracle.

select r.name “Rollback segment name “,
p.pid “Oracle Pid”,
p.spid “System Pid”,
nvl(p.username,’No Transaction’),
p.terminal
from v$lock l, v$process p,v$rollname r
where l.sid=p.pid(+)
and trunc(l.id1(+)/65536)=r.usn
and l.type(+)=’TX’
and l.lmode(+)=6
order by r.name
/