-- Session总体等待

SELECT a.SID,

       a.USERNAME,

       a.MACHINE,

       a.TERMINAL,

       b.EVENT,

       b.TOTAL_WAITS,

       b.TOTAL_TIMEOUTS

  FROM v$session a, V$SESSION_EVENT b

 WHERE a.SID = b.SID

   AND a.STATUS = 'ACTIVE'

   AND user# > 0;



-- Session当前等待

SELECT a.SID,

       a.SERIAL#,

       a.USERNAME,

       a.MACHINE,

       a.TERMINAL,

       b.EVENT,

       b.WAIT_TIME,

       b.SECONDS_IN_WAIT,

       b.STATE

  FROM v$session a, V$SESSION_wait b

 WHERE a.SID = b.SID

   AND a.STATUS = 'ACTIVE'

   AND user# > 0;



-- Session当前正在执行语句

SELECT a.SID,

       a.SERIAL#,

       a.USERNAME,

       a.MACHINE,

       a.TERMINAL,

       b.PIECE,

       b.SQL_TEXT

  FROM v$session a, v$sqltext b

 WHERE b.ADDRESS =

       DECODE(a.SQL_HASH_VALUE, 0, a.PREV_SQL_ADDR, a.SQL_ADDRESS)

   AND a.STATUS = 'ACTIVE'

   AND user# > 0

 ORDER BY a.SQL_ADDRESS, b.PIECE;



-- Session当前等待

SELECT a.SID, a.USERNAME, a.MACHINE, a.TERMINAL, c.NAME, b.VALUE

  FROM v$session a, V$sesstat b, v$statname c

 WHERE a.SID = b.SID

   AND b.STATISTIC# = c.STATISTIC#

   AND a.STATUS = 'ACTIVE'

   AND user# > 0

   AND b.VALUE > 0;