-- Rollback segments - list users/processes using them
-- @oracle\tuning\rbs_users.sql
--

set pagesize 1000
set echo on

column rbs           format a6
column oracle_user   format a12     heading "Oracle|Username"
column sid           format 99999   heading "Oracle|SID"
column serial#       format 99999   heading "Oracle|Serial#"
column unix_pid      format a6      Heading "Unix|PID"
column client_User   format a12     Heading "Client|Username"
column unix_user     format a12     Heading "Unix|Username"

select r.name rbs, nvl(s.username, 'None') oracle_user, s.osuser  client_user, p.username unix_user, s.sid   sid,
       s.serial# serial#, p.spid  unix_pid
from  v$process p, 
      v$rollname r, 
      v$session s,
      v$transaction t
where   s.taddr=t.addr
   and  s.paddr = p.addr(+)
   and  r.usn=t.xidusn(+)
order by r.name;

-- exit