DB常用指令碼 - 查詢正在執行的active session及對應sql ,等待事件

SELECT   a.username,
         a.sid,
         a.serial#,
         p.PID,
         a.status,
         a.MACHINE,
         a.EVENT,
         e.wait_class,
         a.program,
         b.SQL_TEXT,
         b.SQL_FULLTEXT
  FROM   v$session a, v$sqlarea b, v$process p,v$event_name e
 WHERE       a.sql_address = b.address
         AND a.status = 'ACTIVE'
         AND TYPE = 'USER'
         AND A.PADDR = P.ADDR
         AND a.event=e.name
         AND e.wait_class<>'Idle' ;

 ----- 

SELECT   SUBSTR (s.username, 1, 18) username,
         s.sid,
         s.serial#,
         s.machine,
         y.sql_text
  FROM   v$session s,
         v$process p,
         v$transaction t,
         v$rollstat r,
         v$rollname n,
         v$sql y
WHERE       s.paddr = p.addr
         AND s.taddr = t.addr(+)
         AND t.xidusn = r.usn(+)
         AND r.usn = n.usn(+)
         AND s.username IS NOT NULL
         AND s.sql_address = y.address
         --AND s.status = 'ACTIVE'          
          AND TYPE = 'USER'         
            AND  SQL_TEXT LIKE '%exchange%'
         ---and s.sid=56
            ORDER BY   s.sid,  s.serial#,  s.username, s.status   ;