OraQA

The following script provides a complete information about the active sessions on your Oracle database (useful for 9i version).

/*
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
*/

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
/

Filed under Administration, SQL. Follow any responses to this entry through this RSS 2.0 feed.