Script to Check RollBack segment information in oracle Database

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