Query to find the active session in oracle database

The below query shows the currently active session in the oracle database with its wait event ,module, sql_id and elapsed time of the session. 

set pages 1000
set linesize 180
col username for a10
col module for a20
col event for a30
col status for a8
col action for a15
SELECT a.inst_id,
       a.sid,
       b.serial#,
       Substr(b.username, 1, 10)     username,
       Substr(b.MODULE, 1, 20)       MODULE,
       a.p1,
       a.event,
       b.sql_id,
       b.status                      status,
       Round(b.last_call_et / 60, 2) last_call,
       Substr(b.action, 1, 15)       action
FROM   gv$session_wait a,
       gv$session b
WHERE  a.event NOT IN (
        'SQL*Net message from client', 'wakeup time manager', 'pipe get',
        'rdbms ipc message','Streams AQ: qmn slave idle wait',
        'jobq slave wait','PL/SQL lock timer', 'queue messages',
        'Streams AQ: waiting for messages in the queue','smon timer',
        'Streams AQ: qmn coordinator idle wait','VKTM Logical Idle Wait',
        'DIAG idle wait','ges remote message','GCR sleep',
        'Streams AQ: waiting for time management or cleanup tasks',
        'Streams AQ: emn coordinator idle wait','EMON slave idle wait',
        'Space Manager: slave idle wait', 'null event', 'pmon timer',         
        'class slave wait','ASM background timer', 'gcs remote message',
        'wait for unread message on broadcast channel',
        'LNS ASYNC end of log', 'PING' )
       AND a.sid = b.sid
       AND b.status = 'ACTIVE'
       AND a.inst_id = b.inst_id
ORDER  BY a.event;

Comments