Title: Monitoring Growing Rollback Segments

Author: Bruno Vroman, a database administrator for L.C.I. in Maastricht, The Netherlands.

When a large transaction begins, its rollback segment will grow and grow. It is usefull to detect if the RBS is coming close to its
limit before the ORA-1628 error happens (or worse, ORA-1550). This script gives the state of the RBS bigger than a given
size. Note that this threshold is a optional parameter.

REM ***************************************************************/
REM File    : rbs.sql
REM Author  : Bruno Vroman
REM Creation: 26-JAN-1999    Last. modif.: 26-JAN-1999 Bruno Vroman
REM Object  : produce a report of the RBS occupying more than a REM           given threshold. Various sum(space) are given.
REM Sample output:
REM               ROLLBACK SEGMENTS USING MORE THAN 200 MEGABYTES
REM
REM Tablespace   Nb  Size of extent Rbs Name         Mega
REM ---------- ---- --------------- ---------- ----------
REM RBS          24       2,129,920 R16            48.750
REM              80       4,218,880               321.875
REM            ----                 ********** ----------
REM             104                 sum           370.625
REM
REM             479       2,129,920 all rbs.      972.969
REM             103       4,218,880               414.414
REM            ----                 ********** ----------
REM             582                 sum         1,387.383
REM
REM
REM RBSBIG       37      16,793,600 all rbs.      592.578
REM            ----                 ********** ----------
REM              37                 sum           592.578
REM
REM
REM SYSTEM        3          81,920 all rbs.        0.234
REM            ----                 ********** ----------
REM               3                 sum             0.234
REM
REM
REM ********** ----                            ----------
REM sum         726                             2,350.820
****************************************************************/
clear break
clear compute
clear col
col nb         for 999            hea 'Nb'
col bytes      for 99,999,999,999 hea 'Size of extent'
col mega       for 9,990.999      hea 'Mega'
col segnam     for A10            hea 'Rbs Name'
col tablespace for A10            hea 'Tablespace'
break on tablespace skip 1 on segnam skip 1 on report
compute sum of nb mega on segnam report

define _nbmeg=60
accept x prompt "Rollback segment size threshold in MB [&_nbmeg] ? "
col nbmeg noprint new_value _nbmeg
set pause off
set ver off
select to_number( decode( '&x', null, '&_nbmeg', '&x'  ) ) nbmeg from dual;

set pause 'Return to continue...'
set pause on
set pages 23

ttitle ce "ROLLBACK SEGMENTS USING MORE THAN " &_nbmeg -
          " MEGABYTES" skip 1 line

select tablespace_name tablespace, count(*) nb, bytes
     , segment_name segnam, count(*) * bytes / 1024 / 1024 Mega
  from dba_extents a
  where segment_type = 'ROLLBACK'
    and segment_name not like 'RBSBIG%OR_ALLRBS'
    and &_nbmeg * 1048576 < (select sum(b.bytes)
                             from dba_extents b
                             where b.segment_type = 'ROLLBACK'
                               and a.segment_name = b.segment_name)
  group by tablespace_name, bytes, segment_name
union
  select tablespace_name, count(*), bytes, 'all rbs.'
       , count(*) * bytes / 1024 / 1024 Mega
    from dba_extents a
    where segment_type = 'ROLLBACK'
      and segment_name not like 'RBSBIG%OR_ALLRBS'
  group by tablespace_name, bytes, 'all rbs.'
order by 1, 4, 3
;
ttitle off
set pause off