www.pudn.com > oramon.zip > rbsmon.sql

set feedback off 
set verify off 
set serveroutput on 
declare 
 
t_number1 number :=0; 
t_lines   number :=&1; 
 
cursor c_rbs is 
select name 
      ,hwmsize/1024/1024 hwmsize 
      ,nvl(optsize/1024/1024,0) optsize 
      ,wraps 
      ,extends 
      ,shrinks 
      ,aveshrink/1024/1024 aveshrink 
      ,aveactive/1024/1024 aveactive 
from v$rollstat, v$rollname 
where v$rollstat.usn = v$rollname.usn 
order by 1; 
 
begin 
 dbms_output.enable(100000); 
 
 dbms_output.put_line('ROLLBACK SEGMENT HWMSIZE OPTSIZE  WRAPS EXTENDS SHRINKS AVGSHRINK AVGACTIVE'); 
 t_number1 := 0; 
 for r_rbs in c_rbs  
 loop 
   dbms_output.put_line(rpad(ltrim(r_rbs.name),16)                           || 
                        lpad(ltrim(to_char(r_rbs.hwmsize,'9990.00')),8)      || 
                        lpad(ltrim(to_char(r_rbs.optsize,'9990.00')),8)      || 
                        lpad(ltrim(to_char(r_rbs.wraps,'999990')),7)         || 
                        lpad(ltrim(to_char(r_rbs.extends,'9999990')),8)      || 
                        lpad(ltrim(to_char(r_rbs.shrinks,'9999990')),8)      || 
                        lpad(ltrim(to_char(r_rbs.aveshrink,'999990.00')),10) || 
                        lpad(ltrim(to_char(r_rbs.aveactive,'999990.00')),10)); 
   t_number1 := t_number1 + 1; 
   exit when t_number1 = t_lines; 
 end loop; 
end; 
/