--查询执行中的存储过程
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;