- Scripts to check Rollback Segments information Rollback segment Information SELECT segmentname, tablespacename, status.
- Process command execution To illustrate the communications between UNIX and Oracle, let’s use the example of a UNIX script that accesses Oracle to display rollback segment information.
- The question is regarding the status of the rollback status. We have a very small sized rollback segments which suits our needs each of 50M about 12 in number. After 1week to 10days, the rollback segment extends and reaches the maxextents of 50. And after reaching the maxextents it will set the status of the rollback to FULL.
- Oracle Training from Don Burleson The best on site ' Oracle training classes' are just a phone call away!You can get personalized Oracle training by Donald Burleson, right at your shop!
Panacea for an Oracle Applications Database Administrator with Real Application Cluster and Data Guard.
Roll Stats
Roll Stats Dnd 5e Roll20
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';
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';
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
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';
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';
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