Complete sessions information

SELECT   SID, serial#, username,
         (SELECT holding_session
            FROM dba_waiters
           WHERE waiting_session = s.SID
             AND ROWNUM = 1
             AND holding_session NOT IN (SELECT waiting_session
                                           FROM dba_waiters)) holding_session,
         DECODE (s.status, 'ACTIVE', ROUND (last_call_et / 60), 0) time_min,
         DECODE (s.status, 'ACTIVE', last_call_et, 0) time_sec,
         (SELECT used_urec
            FROM v$transaction t
           WHERE t.addr = s.taddr) undo_records,
         (SELECT ROUND (sl.sofar / sl.totalwork * 100, 2)
            FROM v$session_longops sl
           WHERE s.SID = sl.SID
             AND s.serial# = sl.serial#
             AND s.status = 'ACTIVE'
             AND sl.time_remaining > 0) progress,
         (SELECT event
            FROM v$session_wait w
           WHERE w.SID = s.SID) wait_event,
        (select round(value/1024/1024,2) ||'M' from v$sesstat where sid=s.sid and statistic# = 20) PGA_SIZE,
        (select round(value/1024/1024,2) ||'M' from v$sesstat where sid=s.sid and statistic# = 15) UGA_SIZE,
        (select value from v$sesstat where sid=s.sid and statistic# = 4) COMMITS,
         (SELECT sql_text
            FROM v$sql t
           WHERE s.sql_address = t.address
             AND s.sql_hash_value = t.hash_value
             AND ROWNUM = 1) sql_used,
         (SELECT MESSAGE
            FROM v$session_longops sl
           WHERE s.SID = sl.SID
             AND s.serial# = sl.serial#
             AND s.status = 'ACTIVE'
             AND sl.time_remaining > 0) long_ops,
         (SELECT    LOWER (ins.instance_name)
                 || '_ora_'
                 || LTRIM (TO_CHAR (a.spid))
                 || '.trc' filename
            FROM v$process a
           WHERE a.addr = s.paddr) trace_file_name,
         resource_consumer_group, s.status, server, osuser, program,
         module, action, nvl(lower(s.machine), ins.host_name) Machine
    FROM v$session s  , v$instance ins
   WHERE username IS NOT NULL
     AND s.status = 'ACTIVE'
     AND s.audsid <> USERENV ('sessionid')
ORDER BY DECODE (s.status, 'ACTIVE', ROUND (last_call_et), 0) DESC
/
 

 

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

/*
SID                     - Session identifier 
SERIAL                  - Session serial number
USERNAME                - Oracle username
HOLDING_SESSION         - Blocker SID (holding a lock on an object for which another session is waiting)
TIME_MIN                - Active time (minutes)
TIME_SEC                - Active time (seconds)
UNDO_RECORDS            - Number of Undo Records
PROGRESS                - progress of work done so far (only for longops operation)
WAIT_EVENT              - current wait event
PGA_SIZE                - PGA size
UGA_SIZE                - UGA size
COMMITS                 - number of commits
SQL_USED                - current/last SQL
LONG_OPS                - Statistics summary message (only for longops operation)
TRACE_FILE_NAME         - name of the trace filename
RESOURCE_CONSUMER_GROUP - Name of the session's current resource consumer group (Resource Manager feature)
STATUS                  - Status of the session
SERVER                  - Server type
OSUSER                  - Operating system client user name
PROGRAM                 - Operating system program name
MODULE                  - Name of the currently executing module
ACTION                  - Name of the currently executing action
MACHINE                 - Operating system machine name
*/