Scripts to check Rollback Segments information
Rollback segment Information
SELECT segment_name, tablespace_name, status
FROM sys.dba_rollback_segs;
SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents
FROM sys.dba_segments
WHERE segment_type = 'ROLLBACK';
SELECT name, xacts "ACTIVE TRANSACTIONS" FROM v$rollname, v$rollstat WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;
SELECT segment_name, tablespace_name, owner
FROM sys.dba_rollback_segs;
SELECT segment_name, segment_type, tablespace_name
FROM sys.dba_segments
WHERE segment_type = 'DEFERRED ROLLBACK';
Shrinking Rollback segment command
ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;
select count(*) from dba_extents where tablespace_name='RBSTS';
Shrinking all rollback Segments
spool shrink_em.sql
select 'alter rollback segment '||segment_name||' shrink to 2;' from dba_rollback_segs where tablespace_name='RBSTS';
spool off
@shrink_em.sql
Number of rollback extents
select count(*) from dba_extents where tablespace_name='RBSTS';
Finding Rollback Segment Size
SQL> select segment_name,sum(bytes) from dba_segments where
> tablespace_name
> = 'RBS' and segment_name
> = 'RBS17' group by segment_name;
>
> SEGMENT_NAME SUM(BYTES)
> ------------------------ ----------
> RBS17 22364160
Finding Rollback Segment Optimal Size
> SQL> select rs.optsize, rs.extents
> 2 from dba_rollback_segs drs,
> 3 v$rollstat rs
> 4 where drs.segment_name = 'RBS17'
> 5 and drs.segment_id = rs.usn;
>
> OPTSIZE EXTENTS
> ---------- ----------
> 22020096 21
Shrinking Rollback Segment
> SQL> alter rollback segment RBS17 shrink to 10M;
>
> Rollback segment altered.
Script to shrink all rollback Segments
-- Script: shrink_rollback_segs.sql
-- Purpose: to shrink all online rollback segments back to optimal
-------------------------------------------------------------------------------
@save_sqlplus_settings
set pagesize 0
set termout off
spool shrink_rollback_segs.tmp
select
'alter rollback segment ' || segment_name || ' shrink;'
from
sys.dba_rollback_segs
where
status = 'ONLINE'
/
spool off
@shrink_rollback_segs.tmp
host rm -f shrink_rollback_segs.tmp -- for Unix
host del shrink_rollback_segs.tmp -- for others
Finding Current Optimal and Suggested Optimal
column name format a30 heading "Rollback Segment"
column optsize format 99999999999 heading "Current Optimal"
column new_opt format 99999999999 heading "Suggested Optimal"
select
n.name,
s.optsize,
( ceil(s.extents * (s.optsize + s.aveshrink)/(s.rssize + p.value))
* (s.rssize + p.value)
/ s.extents
) - p.value new_opt
from
( select
optsize,
avg(rssize) rssize,
avg(extents) extents,
max(wraps) wraps,
max(shrinks) shrinks,
avg(aveshrink) aveshrink
from
sys.v_$rollstat
where
optsize is not null and
status = 'ONLINE'
group by
optsize
) s,
( select
kvisval value
from
sys.x_$kvis
where
kvistag = 'kcbbkl' ) p,
sys.v_$rollstat r,
sys.v_$rollname n
where
s.shrinks > 1 and
s.shrinks > s.wraps / ceil(s.optsize / ((s.rssize + p.value) / s.extents)) and
r.optsize = s.optsize and
r.status = 'ONLINE' and
n.usn = r.usn
/
Generating Shrink commands (Examples)
select b.segment_name,b.tablespace_name,a.extents,a.rssize,a.xacts,a.optsize,a.shrinks,a.wraps,a.status from v$rollstat a, dba_rollback_segs b where b.segment_id = a.usn;
select 'alter rollback segment ' || segment_name || ' shrink;' from sys.dba_rollback_segs where status = 'ONLINE';
SQL> select 'alter rollback segment ' || segment_name || ' shrink;' from sys.dba_rollback_segs where status = 'ONLINE';
'ALTERROLLBACKSEGMENT'||SEGMENT_NAME||'SHRINK;'
-------------------------------------------------------------
alter rollback segment SYSTEM shrink;
alter rollback segment R01 shrink;
alter rollback segment R02 shrink;
alter rollback segment R03 shrink;
alter rollback segment R04 shrink;
SQL> alter rollback segment R01 shrink;
Rollback segment altered.
SQL> alter rollback segment R02 shrink;
Rollback segment altered.
SQL> alter rollback segment R03 shrink;
Rollback segment altered.
SQL> alter rollback segment R04 shrink;
Rollback segment altered.
Enter value for tbs: RBS1
old 11: dba_data_files where tablespace_name in ('&tbs')) where
new 11: dba_data_files where tablespace_name in ('RBS1')) where
Enter value for tbs: RBS1
old 12: tablespace_name in ('&tbs')
new 12: tablespace_name in ('RBS1')
Used Space(MB) allocated size(MB) maximum allowable (MB) effectivefree(MB) % FREE
-------------- ------------------ ---------------------- ----------------- ----------
200 500 500 300 60
SQL> SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents
FROM sys.dba_segments
WHERE segment_type = 'ROLLBACK'; 2 3
SEGMENT_NAME TABLESPACE_NAME (BYTES)/1024/1024 BLOCKS EXTENTS
--------------------------------------------------------------------------------- ------------------------- ----------------- ---------- ----------
SYSTEM SYSTEM 1.328125 170 17
R0 2 256 2
R01 RBS1 50 6400 10
R02 50 6400 10
R03 50 6400 10
R04 50 6400 10
6 rows selected.
SQL> SELECT segment_name, tablespace_name, status
FROM sys.dba_rollback_segs; 2
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------- ----------------
SYSTEM SYSTEM ONLINE
R0 OFFLINE
R01 RBS1 ONLINE
R02 ONLINE
R03 ONLINE
R04 ONLINE
SQL> SELECT segment_name, tablespace_name, (bytes)/1024/1024, blocks, extents
FROM sys.dba_segments
WHERE segment_type = 'ROLLBACK'; 2 3
SEGMENT_NAME TABLESPACE_NAME (BYTES)/1024/1024 BLOCKS EXTENTS
--------------------------------------------------------------------------------- ------------------------- ----------------- ---------- ----------
SYSTEM SYSTEM 1.328125 170 17
R0 2 256 2
R01 RBS1 50 6400 10
R02 345 44160 69
R03 50 6400 10
R04 50 6400 10
6 rows selected.
SQL> SELECT name, xacts "ACTIVE TRANSACTIONS" FROM v$rollname, v$rollstat WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;
no rows selected
SQL> SELECT segment_name, segment_type, tablespace_name
FROM sys.dba_segments
WHERE segment_type = 'DEFERRED ROLLBACK'; 2 3
no rows selected
SQL> select b.segment_name,b.tablespace_name,a.extents,a.rssize,a.xacts,a.optsize,a.shrinks,a.wraps,a.status from v$rollstat a, dba_rollback_segs b where b.segment_id = a.usn;
SEGMENT_NAME TABLESPACE_NAME EXTENTS RSSIZE XACTS Current Optimal SHRINKS WRAPS STATUS
------------------------------ ------------------------- ---------- ---------- ---------- --------------- ---------- ---------- ---------------
SYSTEM SYSTEM 17 1384448 0 0 0 ONLINE
R01 RBS1 10 52420608 0 0 360 ONLINE
R02 69 361750528 0 0 703 ONLINE
R03 10 52420608 0 0 710 ONLINE
R04 10 52420608 0 0 356 ONLINE
