rbk.sql
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