Which RBS is an Oracle session using?

Sometimes you need to see which Oracle sessions are occupying particular rollback segments. You can use this script:

col name for a10
col username for a20
col status for a10
col sql_text for a40
set linesize 132
select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text, e.status
from v$rollname a, v$rollstat b, v$session c, v$sqltext d, v$transaction e
where a.usn = b.usn
and b.usn=e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address
and c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece
/

You may then kill the particular session using the following format:

ALTER SYSTEM KILL SESSION 'sid,serial#';