rbsmon.sql

一个Oracle性能监视工具

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;
/