rem
rem Rollback statistics and status info
rem
rem Biju Thomas
rem
rem 
set feedback off pages 999 lines 80 echo off trims on
ttitle 'Rollback Statisitics and Status'
spool /tmp/rbsinfo.lst
column SEGMENT_NAME format a18 heading "Segment"
column OWNER format a10 heading "Owner"
column TABLESPACE_NAME format a15 heading "Tablespace"
column INITIAL_EXTENT format 999,999 heading "Ini (K)"
Column NEXT_EXTENT format 999,999 heading "Nxt (K)"
Column STATUS format a10 heading "Status"
Column MAX_EXTENTS format 999 heading "Max"
Column RSSIZE format 999,999 heading "RSize(K)"
Column EXTENTS format 9999 heading "Extents"
Column XACTS format 9999 heading "ActiveXn"
Column OPTSIZE format 999,999 heading "OptSize(K)"
Column HWMSIZE format 999,999 heading "HWM (K)"
Column SHRINKS format 999,999 heading "Shrinks"
Column WRAPS format 999,999 heading "Wraps"
select SEGMENT_NAME, OWNER, TABLESPACE_NAME, INITIAL_EXTENT/1024 INITIAL_EXTENT,
       NEXT_EXTENT/1024 NEXT_EXTENT, MAX_EXTENTS, dba_rollback_segs.STATUS,
       RSSIZE/1024 RSSIZE, EXTENTS, XACTS, OPTSIZE/1024 OPTSIZE, 
       HWMSIZE/1024 HWMSIZE, SHRINKS, WRAPS
from   dba_rollback_segs, v$rollstat
where  v$rollstat.usn (+) = dba_rollback_segs.segment_id 
order by status desc, segment_name
/
spool off
ttitle off
clear columns
set feedback on pages 24
prompt 
prompt Output saved at /tmp/rbsinfo.lst
prompt