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

set feedback off 
set verify off 
set serveroutput on 
declare 
 
t_number1 number :=0; 
t_lines   number :=&1; 
t_sid     number :=&2; 
t_string  varchar2(255) := upper('&3'); 
 
cursor c_usrstat is 
 select sta.name 
       ,ses.value 
   from v$sesstat ses 
       ,v$statname sta 
  where sta.statistic# = ses.statistic# 
    and ses.sid = t_sid 
    and upper(sta.name) like '%'||t_string||'%' 
    and value != 0 
    and rownum <= t_lines 
  order by 2 desc; 
 
begin 
 dbms_output.enable(100000); 
 
 -- detailed sessions 
 dbms_output.put_line('STATISTIC                                            VALUE'); 
 for r_usrstat in c_usrstat  
 loop 
   dbms_output.put_line(rpad(ltrim(r_usrstat.name),50)                           || 
                        lpad(ltrim(to_char(r_usrstat.value,'9999990')),8) ); 
 end loop; 
end; 
/