Oracle Sessions Overview (vsession.sql)

What This Script Does

This script provides a concise overview of all Oracle sessions, showing essential identification and status information. It’s designed for quick session analysis when DBAs need to rapidly assess current database activity without overwhelming detail, making it ideal for initial troubleshooting and system monitoring.

Script

rem  vsession.sql
rem
ttitle 'Oracle Sessions'
rem
col sid format 9990 heading 'SID'
col serial# format 99990 heading 'SER'
col user# format 990 heading 'UID'
col username format a12 heading 'USERNAME'
col command format 990 heading 'COMMAND'
col taddr format a8 heading 'TADDR'
col lockwait format a8 heading 'LOCKWAIT'
col status format a8 heading 'STATUS'
col server format a9 heading 'SERVER'
rem
select sid, serial#, user#, username,
       command, taddr, lockwait, status, server
  from v$session;

Usage

-- Run the script in SQL*Plus or SQLcl
@vsession.sql

-- No parameters required
-- Shows all sessions including system sessions
-- Lightweight output for quick scanning

Required Privileges

SELECT on V$SESSION
Available to most database users

Sample Output

Oracle Sessions

  SID   SER UID USERNAME     COMMAND TADDR    LOCKWAIT STATUS   SERVER
----- ----- --- ------------ ------- -------- -------- -------- ---------
    1     1   0 SYS                0 00000000          ACTIVE   DEDICATED
    2     1   0 SYS                0 00000000          ACTIVE   DEDICATED
    3     1   0 SYS                0 00000000          ACTIVE   DEDICATED
   10    15   0 SYS                0 00000000          ACTIVE   DEDICATED
   15    25  45 SCOTT              3 07A2F3C8          ACTIVE   DEDICATED
   18    33  45 SCOTT              0 00000000          INACTIVE DEDICATED
   22    41  67 HR_USER            1 07A2F450          ACTIVE   DEDICATED
   25    52  67 HR_USER           47 00000000          INACTIVE SHARED
   28    67  89 APPUSER            6 07A2F5D8 07A2F600 ACTIVE   DEDICATED
   31    78  89 APPUSER            0 00000000          INACTIVE DEDICATED
   35    89 123 REPORT_USR         0 00000000          ACTIVE   SHARED

Key Output Columns

SID: Session identifier (unique per instance)
SER: Serial number (increments when SID reused)
UID: Internal user ID
USERNAME: Database username (NULL for background processes)
COMMAND: Current SQL command type (numeric code)
TADDR: Transaction address (NULL if no transaction)
LOCKWAIT: Lock wait address (NULL if not waiting)
STATUS: Session status (ACTIVE/INACTIVE)
SERVER: Server type (DEDICATED/SHARED/NONE)

Understanding Session Information

Session Status

ACTIVE: Currently executing SQL
INACTIVE: Connected but not executing
KILLED: Marked for termination
CACHED: Cached in connection pool
SNIPED: Idle timeout exceeded

Server Types

DEDICATED: Dedicated server process
SHARED: Shared server (MTS) connection
NONE: Background process
POOLED: Connection pooling

Command Types (Common Values)

0: No command (idle)
1: CREATE TABLE
2: INSERT
3: SELECT
6: UPDATE
7: DELETE
47: PL/SQL EXECUTE

Performance Analysis

Session Patterns

High SID values: Many connections created
LOCKWAIT not NULL: Sessions waiting for locks
TADDR not NULL: Active transactions
All INACTIVE: Possible connection pooling issues

Resource Usage Indicators

Many ACTIVE sessions: High concurrency
SHARED servers: Shared server configuration
Background processes: System activity level
Lock waits: Contention issues

Common Use Cases

Quick System Overview

-- Get session count by status
@vsession.sql
-- Count ACTIVE vs INACTIVE sessions
-- Look for unusual patterns

Session Identification

-- Find specific user sessions
@vsession.sql
-- Locate sessions by USERNAME
-- Note SID and SERIAL# for further investigation

Lock Wait Detection

-- Identify sessions waiting for locks
@vsession.sql
-- Look for non-NULL LOCKWAIT values
-- Cross-reference with lock analysis scripts

Troubleshooting Session Issues

High Session Count

Count total sessions:

SELECT COUNT(*) total_sessions,
       COUNT(CASE WHEN username IS NOT NULL THEN 1 END) user_sessions,
       COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) active_sessions
FROM v$session;

Check session limit:

SELECT name, value FROM v$parameter
WHERE name = 'sessions';

Connection Pool Analysis

-- Analyze connection patterns
SELECT username,
       server,
       COUNT(*) session_count,
       COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) active,
       COUNT(CASE WHEN status = 'INACTIVE' THEN 1 END) inactive
FROM v$session
WHERE username IS NOT NULL
GROUP BY username, server
ORDER BY session_count DESC;

Session Kill Preparation

-- Prepare to kill specific sessions
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'
FROM v$session
WHERE username = 'TARGET_USER'
AND status = 'INACTIVE';

Advanced Analysis

Session Distribution by User

-- Count sessions per user
SELECT username,
       COUNT(*) total,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) active,
       SUM(CASE WHEN lockwait IS NOT NULL THEN 1 ELSE 0 END) waiting
FROM v$session
WHERE username IS NOT NULL
GROUP BY username
ORDER BY total DESC;

Transaction Analysis

-- Sessions with active transactions
SELECT s.sid, s.serial#, s.username, s.status,
       t.used_ublk undo_blocks,
       t.used_urec undo_records,
       ROUND((SYSDATE - t.start_date) * 24 * 60, 2) minutes_active
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY minutes_active DESC;

Resource Usage Summary

-- Resource usage by session type
SELECT DECODE(username, NULL, 'Background', 'User') session_type,
       server,
       COUNT(*) sessions,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) active,
       SUM(CASE WHEN lockwait IS NOT NULL THEN 1 ELSE 0 END) waiting
FROM v$session
GROUP BY DECODE(username, NULL, 'Background', 'User'), server
ORDER BY sessions DESC;

Detailed Session Information (gvsess.sql) - Comprehensive session details
Session Wait Events (gvsessab.sql) - Session wait analysis
Blocking Sessions (vlockb2.sql) - Lock blocking analysis
Active Sessions (gvsqlplanfind.sql) - SQL execution details