Saturday, February 16, 2019

Active sessions with sql's

SELECT SS.sid,
       SS.serial#,
       SUBSTR(SS.USERNAME, 1, 15) USERNAME,
       SS.OSUSER "USER",
       AR.MODULE || ' @ ' || SS.MACHINE CLIENT,
       SS.PROCESS PID,
       TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
       AR.DISK_READS DISK_READS,
       AR.BUFFER_GETS BUFFER_GETS,
       SUBSTR(SS.LOCKWAIT, 1, 10) LOCKWAIT,
       W.EVENT EVENT,
       SS.STATUS,
       AR.SQL_fullTEXT SQL
  FROM V$SESSION_WAIT W, V$SQLAREA AR, V$SESSION SS
 WHERE SS.SQL_ADDRESS = AR.ADDRESS
   AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
   AND SS.SID = W.SID(+)
   AND SS.STATUS = 'ACTIVE'
   AND W.EVENT != 'client message'
   and SS.username is not null
 ORDER BY AR.LAST_LOAD_TIME DESC,
          SS.LOCKWAIT       ASC,
          SS.USERNAME,
          AR.DISK_READS     DESC;

No comments:

Post a Comment