/* ckuserrbs.sql
     check rollback segments for users and active transactions
*/
set pause off
set pause 'Mash Enter...'
set feedback off
set verify off
col name format a12 heading RollbackSeg
col username format a12 heading DBUser
col sid format 99999 heading DBSID
col serial# format 99999 heading SRL#
col osuser format a8 heading OSUser
col program format a30 heading Program trunc
col spid format a6 heading BOSPID
col bpgm format a20 heading "BackgroundPgm" trunc
col module format a15 trunc
col status format a1 trunc
col machine format a15 trunc
col lastcallet format a10 trunc
col tablespace_name format a15 trunc
accept trgtrbs char default ALL prompt 'Limit to which rollback segment <ALL> : '
accept trgttbs char default ALL prompt 'Limit to which Tablespace <ALL> : '
accept trgtmod char default ALL prompt 'Limit to which module <ALL> : '
accept trgtstatus char default N prompt 'Limit to active sessions only <N> : '
prompt Checking rollback segment info...
select /*+ rule */
       e.tablespace_name, c.name,
       b.username ,
       b.sid , b.status , b.osuser, b.module, b.machine,
	floor(b.last_call_et/3600)||':'||
	floor(mod(b.last_call_et,3600)/60)||':'||
	mod(mod(b.last_call_et,3600),60) "LastCallET",
       d.spid, b.command
from v$lock a, v$session b, v$rollname c, v$process d, dba_rollback_segs e
where a.type='TX'
   and a.sid=b.sid
   and d.addr = b.paddr
   and c.usn=trunc(a.id1/1048596)*16+trunc((mod(a.id1,1048596))/65536)
   and e.segment_name = c.name
   and (c.name = upper('&trgtrbs') or upper('&trgtrbs') = 'ALL')
   and (b.module = upper('&trgtmod') or upper('&trgtmod') = 'ALL')
   and (b.status = 'ACTIVE' and upper('&trgtstatus') = 'Y' or upper('&trgtstatus') = 'N')
   and (e.tablespace_name = upper('&trgttbs') or upper('&trgttbs') = 'ALL')
order by 1,2,5 desc;