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

set feedback off 
set verify off 
set serveroutput on 
declare 
 
t_number1 number :=0; 
t_lines   number :=&1; 
 
cursor c_session is 
select ses.sid 
      ,decode(ses.username,null,'internal',ses.username) username 
      ,decode(ses.osuser,null,'internal',ses.osuser) osuser 
      ,substr(ses.program,1,26) program 
      ,sio.physical_reads IO 
      ,sta.value/100 CPU 
      ,ses.status 
from  v$session ses 
     ,v$sess_io sio 
     ,v$sesstat sta 
     ,v$statname stn 
where ses.SID = sio.SID 
  and sta.STATISTIC# = stn.STATISTIC# 
  and sta.SID = ses.SID 
  and stn.name like '%CPU used by this session%' 
order by 7,&2 desc, 1; 
 
begin 
 dbms_output.enable(100000); 
 
 -- detailed sessions 
 dbms_output.put_line('SID USERNAME  OSUSER      PROGRAM                     DISK I/O     CPU    STATUS'); 
 t_number1 := 0; 
 for r_session in c_session  
 loop 
   dbms_output.put_line(rpad(ltrim(r_session.sid),4)                             || 
                        rpad(ltrim(r_session.username),10)                       || 
                        rpad(ltrim(r_session.osuser),12)                         || 
                        rpad(ltrim(r_session.program),26)                        || 
                        lpad(ltrim(r_session.IO),10)                             || 
                        lpad(ltrim(to_char(r_session.CPU,'9990.00')),8)          || 
                        lpad(ltrim(r_session.status),10)); 
   t_number1 := t_number1 + 1; 
   exit when t_number1 = t_lines; 
 end loop; 
end; 
/