DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 24, 2012

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

No comments: