two scripts - monitoring the usage of rollback segments of Oracle Database

-- to see which PID is using RBS
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;

ROLLBACK SEGMENT NAME          ORACLE PID SYSTEM PID   NVL(P.USERNAME, TERMINAL
------------------------------ ---------- ------------ --------------- ------------------------------
R01                                                    NO TRANSACTION
R02                                                    NO TRANSACTION
R03                                                    NO TRANSACTION
R04                                                    NO TRANSACTION
R05                                                    NO TRANSACTION
R06                                                    NO TRANSACTION
R07                                                    NO TRANSACTION
R08                                                    NO TRANSACTION
SYSTEM                                                 NO TRANSACTION

--SEE rbs with transactions (if you like to kill session) from Steve Zhao:
SELECT s.sid, s.serial#
 FROM v$session s
 WHERE s.saddr in
  (SELECT t.ses_addr
    FROM V$transaction t, dba_rollback_segs r
    WHERE t.xidusn=r.segment_id
    AND r.tablespace_name='<Your RBTBS Name>');

SELECT s.sid, s.serial#, r.segment_name
FROM v$session s, dba_rollback_segs r, V$transaction t
WHERE s.saddr = t.ses_addr
AND  t.xidusn=r.segment_id
AND r.tablespace_name='<Your RBTBS Name>'
;