监控查询超过5分钟的SQL语句

plan_operation = 'SELECT STATEMENT'条件可以替换成'UPDATE STATEMENT','DELETE STATEMENT'用于监控长时间update,delete语句

set linesize 200 pagesize 200
col event for a30
col machine for a15
col username for a12
col spid for a8
col program for a20
col event for a15
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select /*XJ LEADING(S) FIRST_ROWS */
---s.logon_time,
 s.sql_exec_start,
 s.sid,
 s.serial#,
 p.spid,
 s.username,
 s.machine,
 s.program,
 s.event,
 ---S.P1 || '/' || S.P2 || '/' || S.P3 P123,
 ---S.wait_time,
 NVL(s.SQL_ID, S.PREV_SQL_ID) SQL_ID
  from v$session s, v$process p, v$sql_plan_monitor sql
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sql_id = sql.sql_id
   and sql.sql_exec_id = s.sql_exec_id
   and sql.plan_line_id = '0'
   and plan_operation = 'SELECT STATEMENT'
   and s.sid <> userenv('sid')
   and sysdate - s.sql_exec_start >= 5 / 1440
 order by p.pid desc;