查询执行中的存储过程



--查询执行中的存储过程
select
       b.logon_time,
       decode(a.program_id,0,to_char(null),
       (select obj.object_type || '       ' || obj.owner || '.' || obj.object_name
         from dba_objects obj where obj.object_id = a.program_id and rownum = 1 )) as program,
       a.program_line#,
       b.osuser,
       c.spid,
       b.sid,
       b.USERNAME,
       a.sql_id,
       a.SQL_TEXT,
       a.SQL_FULLTEXT,
      a.EXECUTIONS ex,
        a.ROWS_PROCESSED,
      trunc( a.ROWS_PROCESSED/case when a.EXECUTIONS =0 then 1 else a.EXECUTIONS  end) as "rows/exe" ,
      round(buffer_gets/decode(a.executions,0,1,a.executions))  "buffer/exe" ,
      round(disk_reads/decode(a.executions,0,1,a.executions)) "disk/exe" ,
       a.FIRST_LOAD_TIME,
       a.LAST_ACTIVE_TIME,
       b.MACHINE,
       b.MODULE,
       AUDSID,
       b.EVENT,
       b.STATE,
       b.WAIT_TIME,
       b.SECONDS_IN_WAIT,      
       c.PGA_ALLOC_MEM,
       b.service_name,
       'alter system kill session ''' || to_char(b.SID) || ',' ||
       to_char(b.SERIAL#) || ''';' killse,
       'select * from table(dbms_xplan.display_cursor(''' || a.SQL_ID || ''',' || a.CHILD_NUMBER || ',''ALL'')); --IOSTATS MEMSTATS ALLSTATS LAST  RUNSTATS_TOT  RUNSTATS_LAST outline ' as scan_sql_plan_sql
  from  v$sql a,  v$session b,  v$process c
 where a.SQL_ID = b.SQL_ID
   and b.PADDR = c.ADDR
   and b.STATUS='ACTIVE'
   AND B.SID<>(SELECT SID FROM V$MYSTAT WHERE ROWNUM<2)
 order by b.username, a.SQL_TEXT;