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>'
;