rbs_info.sql
Download Script
SET ECHO off
REM NAME: rbsinfo.sql
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT n V$rollname, V$rollstat, & sys.dba_rollback_segs
REM --------------------------------------------------------------------------
REM PURPOSE:
REM To give detailed information about the rollback segements in a database
REM ---------------------------------------------------------------------------
REM EXAMPLE:
REM All Rollback Segments
REM
REM Segm Name Ownr In TabSpace File containing header of rbs
REM --------------- ---- ------------ -------------------------------
REM SYSTEM Priv SYSTEM C:\ORANT\DATABASE\SYS1ORCL.ORA
REM RB_TEMP Priv SYSTEM C:\ORANT\DATABASE\SYS1ORCL.ORA
REM RB1 Publ ROLLBACK_DAT C:\ORANT\DATABASE\RBS1ORCL.ORA
REM RB2 Publ ROLLBACK_DAT C:\ORANT\DATABASE\RBS1ORCL.ORA
REM RB3 Publ ROLLBACK_DAT C:\ORANT\DATABASE\RBS1ORCL.ORA
REM RB4 Publ ROLLBACK_DAT C:\ORANT\DATABASE\RBS1ORCL.ORA
REM
REM Online Rollback Segments:
REM
REM Name NrEx Size Init Next PctI MinE MaxE Opt size Stat
REM ------- ---- ------- -------- -------- ---- ---- ---- ----------- ----
REM SYSTEM 4 198K 51,200 51,200 0 2 121 OnL
REM RB1 64 3198K 51,200 51,200 0 2 121 OnL
REM RB2 68 3406K 51,200 51,200 0 2 121 OnL
REM RB3 119 5948K 51,200 51,200 0 2 121 OnL
REM RB4 76 3798K 51,200 51,200 0 2 121 OnL
REM -------------------------------------------------------------------------
REM Main text of script follows:
set feed off
set pause off
set lines 80
col nm format a11 heading 'Name' trunc
col ex format 99999 headin 'NrEx'
col rs format a7 heading 'Size'
col init format a7 heading 'Init'
col next format a7 heading 'Next'
col mi format 999 heading 'MinE'
col ma format a10 heading 'MaxE'
col op format a7 heading 'Optimal'
col id format 99 heading 'ID'
col st format a10 heading 'Status'
col sn format a14 heading 'Segm Name'
col ts format a12 heading 'In TabSpace'
col fn format a45 heading 'File containing header of rbs'
col ow format a4 heading 'Ownr'
prompt All Rollback Segments
select segment_name sn, decode(owner,'PUBLIC','Publ','Priv') ow,
tablespace_name ts, name fn
from sys.dba_rollback_segs d, v$datafile f
where d.file_id = f.file#;
prompt
select d.segment_name nm,
s.extents ex,
(s.rssize/1024)||'K' rs,
(d.initial_extent/1024)||'K' init,
(d.next_extent/1024)||'K' next,
(s.optsize/1024)||'K' op,
d.segment_id id,
decode ( d.max_extents, 2147483645, 'Unlimited', d.max_extents ) ma,
s.status st
from v$rollname n, v$rollstat s, sys.dba_rollback_segs d
where n.usn = s.usn
and d.segment_name = n.name(+);
rem d.min_extents mi,
set pages 1000
col file_name for a40
break on file_name skip 1 on report
compute sum of free_space on report
select df.file_name file_name, sum(fs.bytes) free_space
from dba_free_space fs, dba_data_files df
where (fs.tablespace_name like 'ROLLBACK%' or fs.tablespace_name like 'RBS%')
and fs.file_id = df.file_id
group by file_name
order by df.file_name;
set feed on
