Learn. Build. Share. Grow — together.

How to Check Active Sessions in Oracle Database

Google Add W3Buddy as a preferred source on Google
Article Summary

Learn how to check active sessions in Oracle Database with optimized SQL queries. Includes blocking sessions, wait events, and RAC monitoring for DBAs.

Monitoring active sessions is a critical day-to-day task for Oracle DBAs. This script provides comprehensive information about all currently active sessions, helping you identify resource-intensive queries, troubleshoot performance issues, and monitor user activity.

-- ====================================================================
-- Script: active_sessions_monitor.sql
-- Purpose: Comprehensive active session monitoring for Oracle DBAs
--Author: W3Buddy
-- ====================================================================

SET LINESIZE 300
SET PAGESIZE 100
SET COLSEP '|'
SET VERIFY OFF
SET FEEDBACK OFF
SET TRIMOUT ON
SET TRIMSPOOL ON

-- Column Formatting
COLUMN inst_id FORMAT 9999 HEADING "INST|ID"
COLUMN sid FORMAT 99999 HEADING "SID"
COLUMN serial# FORMAT 99999 HEADING "SERIAL#"
COLUMN ora_pid FORMAT 99999 HEADING "ORA|PID"
COLUMN os_pid FORMAT A10 HEADING "OS|PID"
COLUMN username FORMAT A15 HEADING "DB USERNAME" TRUNCATE
COLUMN status FORMAT A8 HEADING "STATUS"
COLUMN duration_min FORMAT 9999.99 HEADING "ACTIVE|MIN"
COLUMN osuser FORMAT A12 HEADING "OS USER" TRUNCATE
COLUMN machine FORMAT A20 HEADING "CLIENT MACHINE" TRUNCATE
COLUMN program FORMAT A25 HEADING "PROGRAM" TRUNCATE
COLUMN module FORMAT A20 HEADING "MODULE" TRUNCATE
COLUMN action FORMAT A15 HEADING "ACTION" TRUNCATE
COLUMN sql_id FORMAT A13 HEADING "SQL_ID"
COLUMN blocking_info FORMAT A20 HEADING "BLOCKING|INFO"
COLUMN wait_event FORMAT A25 HEADING "WAIT EVENT" TRUNCATE
COLUMN logon_time FORMAT A16 HEADING "LOGON TIME"
COLUMN cpu_time FORMAT 9999.99 HEADING "CPU|SEC"

PROMPT
PROMPT ========================================================================
PROMPT                    ACTIVE SESSIONS MONITORING REPORT
PROMPT ========================================================================
PROMPT

SELECT 
    s.inst_id,
    s.sid,
    s.serial#,
    p.pid AS ora_pid,
    p.spid AS os_pid,
    s.username,
    s.status,
    ROUND(s.last_call_et/60, 2) AS duration_min,
    s.osuser,
    s.machine,
    s.program,
    s.module,
    s.action,
    s.sql_id,
    CASE 
        WHEN s.blocking_session IS NOT NULL 
        THEN TO_CHAR(s.blocking_session) || 
             DECODE(s.blocking_instance, NULL, '', ',@' || s.blocking_instance)
        ELSE NULL 
    END AS blocking_info,
    s.event AS wait_event,
    TO_CHAR(s.logon_time, 'DD-MON-YY HH24:MI') AS logon_time,
    ROUND(st.cpu_value/1000000, 2) AS cpu_time
FROM 
    gv$session s
    JOIN gv$process p ON s.paddr = p.addr AND s.inst_id = p.inst_id
    LEFT JOIN (
        SELECT 
            inst_id, 
            sid, 
            SUM(CASE WHEN statistic# = 12 THEN value ELSE 0 END) AS cpu_value
        FROM gv$sesstat
        WHERE statistic# = 12
        GROUP BY inst_id, sid
    ) st ON s.inst_id = st.inst_id AND s.sid = st.sid
WHERE 
    s.username IS NOT NULL
    AND s.status = 'ACTIVE'
    AND s.type = 'USER'
ORDER BY 
    s.inst_id,
    duration_min DESC,
    s.username;

PROMPT
PROMPT ========================================================================
PROMPT                    ACTIVE SESSIONS SUMMARY BY INSTANCE
PROMPT ========================================================================
PROMPT

SELECT 
    inst_id AS "INST_ID",
    COUNT(*) AS "ACTIVE_SESSIONS",
    COUNT(CASE WHEN blocking_session IS NOT NULL THEN 1 END) AS "BLOCKED_SESSIONS",
    ROUND(AVG(last_call_et/60), 2) AS "AVG_ACTIVE_MIN",
    ROUND(MAX(last_call_et/60), 2) AS "MAX_ACTIVE_MIN"
FROM 
    gv$session
WHERE 
    username IS NOT NULL
    AND status = 'ACTIVE'
    AND type = 'USER'
GROUP BY 
    inst_id
ORDER BY 
    inst_id;

PROMPT
PROMPT ========================================================================
PROMPT                    TOP WAIT EVENTS FOR ACTIVE SESSIONS
PROMPT ========================================================================
PROMPT

SELECT 
    event AS "WAIT_EVENT",
    COUNT(*) AS "SESSION_COUNT",
    ROUND(AVG(wait_time/100), 2) AS "AVG_WAIT_SEC"
FROM 
    gv$session
WHERE 
    username IS NOT NULL
    AND status = 'ACTIVE'
    AND type = 'USER'
    AND event IS NOT NULL
GROUP BY 
    event
ORDER BY 
    COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

PROMPT
PROMPT ========================================================================
SET FEEDBACK ON

Sample Output

========================================================================
                    ACTIVE SESSIONS MONITORING REPORT
========================================================================

INST|  SID|SERIAL#|ORA|OS        |DB USERNAME     |STATUS  |  ACTIVE|OS USER      |CLIENT MACHINE       |PROGRAM                   |MODULE               |ACTION          |SQL_ID        |BLOCKING   |WAIT EVENT                |LOGON TIME       |   CPU|  WAIT|
  ID|     |       |PID|PID       |                |        |     MIN|             |                     |                          |                     |                |              |SID,SER#   |                          |                 |   SEC|   SEC|
----|-----|-------|---|----------|----------------|--------|--------|-------------|---------------------|--------------------------|---------------------|----------------|--------------|-----------|--------------------------|-----------------|------|------|
   1|  245|  12456| 89|23145     |APPUSER         |ACTIVE  |   12.50|oracle       |app-server-01        |JDBC Thin Client          |OrderProcessing      |processOrder    |8xyk2m3np4q7s|           |db file sequential read   |10-FEB-26 08:30  | 45.23|120.56|
   1|  387|  45123|112|23678     |RPTUSER         |ACTIVE  |    5.25|reportuser   |rpt-server-02        |sqlplus@rpt-server-02     |MonthlyReport        |generateReport  |a9mz5k2lp8w3x|           |direct path read          |10-FEB-26 09:15  | 22.10| 98.42|
   1|  512|  23890|145|24012     |WEBUSER         |ACTIVE  |    2.10|webadmin     |web-server-03        |Apache Tomcat             |WebTransaction       |checkout        |f3np8q2mk5z7y|245,12456  |enq: TX - row lock        |10-FEB-26 09:42  |  5.67|115.34|
   2|  156|  67234| 78|25145     |BATCHUSER       |ACTIVE  |   15.75|batch        |batch-server-01      |python@batch-server-01    |DataLoad             |loadCustomers   |k7zt3n8mp2q5w|           |direct path write         |10-FEB-26 08:15  | 67.89| 45.23|
   2|  289|  34567| 92|26234     |APPUSER         |ACTIVE  |    1.50|oracle       |app-server-02        |JDBC Thin Client          |OrderProcessing      |updateInventory |m2np5k8zq3w7x|           |log file sync             |10-FEB-26 10:05  |  8.45| 12.67|

========================================================================
                    ACTIVE SESSIONS SUMMARY BY INSTANCE
========================================================================

INST_ID ACTIVE_SESSIONS BLOCKED_SESSIONS AVG_ACTIVE_MIN MAX_ACTIVE_MIN
------- --------------- ---------------- -------------- --------------
      1               3                1           6.62          12.50
      2               2                0           8.63          15.75

========================================================================
                    TOP WAIT EVENTS FOR ACTIVE SESSIONS
========================================================================

WAIT_EVENT                SESSION_COUNT AVG_WAIT_SEC
------------------------- ------------- ------------
db file sequential read               1        12.06
enq: TX - row lock                    1        11.53
direct path read                      1         9.84
direct path write                     1         4.52
log file sync                         1         1.27

========================================================================

Notes for DBAs

Query Features

  • RAC Compatible: Uses gv$ views with proper instance joins
  • Comprehensive Metrics: Includes CPU time, wait time, and blocking information
  • Multi-Section Output: Main report, summary statistics, and top wait events
  • Performance Optimized: Efficient joins and filtering

Key Columns Explained

  • ACTIVE MIN: How long the session has been active (in minutes)
  • BLOCKING SID,SER#: If populated, this session is blocked by another session
  • CPU SEC: Total CPU time consumed by the session
  • WAIT SEC: Total wait time experienced by the session
  • SQL_ID: Identifier to query v$sql or v$sqltext for full SQL text

Common Use Cases

1. Find specific user sessions:

AND UPPER(s.username) = 'APPUSER'

2. Find long-running sessions (>10 minutes):

AND s.last_call_et/60 > 10

3. Find sessions from specific machine:

AND UPPER(s.machine) LIKE '%APP-SERVER%'

4. Exclude specific programs (like SQL*Plus):

AND s.program NOT LIKE 'sqlplus%'

Related Queries

Get SQL text for a session:

SELECT sql_text 
FROM v$sqltext 
WHERE sql_id = '8xyk2m3np4q7s' 
ORDER BY piece;

Kill a session:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- Example: ALTER SYSTEM KILL SESSION '245,12456' IMMEDIATE;

-- For RAC:
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;
-- Example: ALTER SYSTEM KILL SESSION '245,12456,@1' IMMEDIATE;

Find what SQL is blocking:

SELECT 
    blocking_session,
    sid,
    serial#,
    wait_class,
    seconds_in_wait
FROM 
    v$session
WHERE 
    blocking_session IS NOT NULL
ORDER BY 
    seconds_in_wait DESC;

Performance Tips

  • On high-volume systems, use v$session instead of gv$session for single-instance databases
  • The gv$sesstat join adds CPU/wait metrics but may slow the query on very busy systems
  • Consider removing the statistics join if you only need basic session info
  • Run during off-peak hours for detailed historical analysis

Troubleshooting Guide

  • No output: Check if there are any active user sessions (SELECT COUNT(*) FROM v$session WHERE status='ACTIVE')
  • Slow query: Remove the gv$sesstat join or limit to specific users
  • RAC issues: Ensure inst_id joins are present in all gv$ view joins
Was this helpful?