set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 135
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT ####################################################################
PROMPT --  Name          :  listRollbackSegmentInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This script lists the Rollback segments and their tablespaces.
PROMPT 
PROMPT --  Inputs(s)     :  None
PROMPT 
PROMPT --  Output       : The output is in a file named rollbackSegmentInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT 
PROMPT ####################################################################
PROMPT

column pname       format a44        heading "Parameter Name"  wrap
column value       format a44        heading "Parameter Value" wrap
column max_extents                   heading "Max|Extents"
column next_extent format 99,999.999 heading "Next|Extent (MB)"
column optsize     format 99,999.999 heading "Optimal|Size (MB)"
column count                         heading "Number|of Waits"
column time                          heading "TOTAL Time|of Waits"

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool rollbackSegmentInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "Overall Rollback Segment Info in "  &s_dbname " on " &s_machine skip 2

select name pname,
       value
from v$parameter
where name IN ('transactions_per_rollback_segment', 'max_rollback_segments', 'rollback_segments');

ttitle left &s_curDate -
       center "Overall WAIT Statistics for RBs in "  &s_dbname " on " &s_machine skip 2

select class, 
       count,
       time 
from v$waitstat
where class = '%undo%';

ttitle left &s_curDate -
       center "List of Rollback Segments and their tablespaces in "  &s_dbname " on " &s_machine skip 2

break on tablespace_name skip 1
 
select tablespace_name,
       segment_name,
       status
from dba_rollback_segs
order by tablespace_name;

ttitle left &s_curDate -
       center "Runtime information on Rollback Segments in "  &s_dbname " on " &s_machine skip 2

select segment_name,
       extents,
       max_extents,
       next_extent/(1024*1024) next_extent,
       optsize/(1024*1024) optsize,
       shrinks,
       wraps,
       extends
from dba_rollback_segs drs,
     v$rollname rn,
     v$rollstat rs
where drs.segment_name = rn.name
  and rn.usn = rs.usn;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no rollback segment information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file rollbackSegmentInfo_&s_dbname._&s_machine..LST produced
PROMPT

@sqlplus_settings