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