/*****************************************
* File:    rollinfo.sql
* Author:  P.Noonan (SGA Consulting Ltd )
******************************************/
col segment_name format a10 Head "ROLL SEG" 
col xtnts format 9999
col opt_mb format 99999
col size_mb format 99999.99
col init_mb format 9999.99
col next_mb format 9999.99
col hwm_mb format 99999.99
col xacts format 9999
col waits format 99999
col sta format a3
select 	ds.segment_name
	,ds.bytes / 1048576 size_mb
	,ds.extents xtnts
	,ds.initial_extent / 1048576 INIT_MB
	,ds.next_extent / 1048576 NEXT_MB
        ,rs.optsize / 1048576 OPT_MB
        ,rs.hwmsize / 1048576 hwm_mb
        ,rs.xacts
        ,rs.waits
        ,decode(rs.status,'ONLINE','ON','OFF') STA
from dba_segments ds,
     v$rollname rn,
     v$rollstat rs
where ds.segment_type = 'ROLLBACK'
  and ds.segment_name = rn.name(+)
  and rs.usn(+) = rn.usn
/