clear col
set head off
set pause off
set pages 0
set verify off
set feedback off

clear breaks
clear compute

column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,8) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 120 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'DB Review(5) - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

set head on
set verify on
set feedback on
col table_name format a30
col ext format 990
col used format 99,990
col empty format 99,990
col used_pct format 990.99
col next format 99,999,990
spool db_revw5a.rslt 

break on "OWNER" on SegmentName on Tablespace;
set echo on;

/* ****************************************** */
/*  SECTION 1 - Rollback Segment Information  */
/* ****************************************** */

set echo off;

column SegmentName  format A10      heading 'Segment|Name' 
column Tablespace   format A8       heading 'Tblspace'
column CurrentExt   format 9,999      heading 'Cur |Exts' 
column InitialExt   format 99,999.999  heading 'Initial|Extent|(MB)' 
column NextExt      format 99,999.999  heading 'Next|Extent|(MB)'  
column SizeExt      format 99,999.999  heading 'Total|Size|(MB)' 
column SizeOpt      format 99,999.999  heading 'Opt|Size|(MB)' 
column MinExt       format 999      heading 'Min|Ext'  
column MaxExt       format 9999     heading 'Max|Ext' 
column Status       format A10      heading 'STATUS' 
column AveShrink    format 999.999  heading 'Ave|Shrink|(MB)' 


select  substr(owner,1,10)                "OWNER",
	substr(segment_name,1,10)	  SegmentName,
        substr(tablespace_name,1,10)      Tablespace,
        min_extents - 1	                  CurrentExt,
        initial_extent/(1024*1024)        InitialExt,
        next_extent/(1024*1024)	          NextExt,
        MIN_EXTENTS			  MinExt,                     
        MAX_EXTENTS 			  MaxExt,                  
	status				  status
from dba_rollback_segs
order by 1, 2,3,4,5;   

set echo on;
/* ****************************************** */
/*  SECTION 2 - Rollback Segment Sizing       */
/* ****************************************** */
set echo off;

select substr(B.owner,1,10)         "OWNER",
      substr(A.segment_name,1,10)	SegmentName,
	A.bytes/(1024*1024) 		SizeExt,       
	A.extents 	                  CurrentExt,
	A.min_extents			MinExt,
	A.max_extents 			MaxExt
from dba_segments A,
     dba_rollback_segs B
where A.segment_type = 'ROLLBACK' and
     A.segment_name = B.segment_name
order by 1, 2,3,4,5;   
    
set echo on;
/* ****************************************** */
/*  SECTION 3 - Optimial Rollback Segment     */
/* ****************************************** */
set echo off;

select substr(name,1,10) 	      SegmentName,
	optsize/(1024*1024) 		SizeOpt,       
	shrinks 	                  ,
	aveshrink/(1024*1024) 	      AveShrink,
	wraps 		      	,
      extends
from v$rollstat A,
     v$rollname B
where A.USN = B.USN
order by 1, 2,3,4,5;   
 
clear breaks;
clear compute;
set head on;
set pause off;
spool off;
ttitle off;
commit;
