All User Sessions Analysis (gvuserall.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

Provides comprehensive search and analysis capabilities for user sessions across all RAC instances, combining session information with process details and I/O statistics. The script offers extensive filtering options and orders results by I/O activity to identify the most resource-intensive sessions.

The Script

rem  gvuserall.sql
rem
ttitle 'Global User Sessions Search'
rem
set linesize 180
rem
clear break
rem
col inst_id format 999 heading 'INST'
col pid format 999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 99990 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col sid_serial format a10 heading 'SID,SERIAL#'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a15 heading 'ORACLE|USERNAME'
col logical format b999999999 heading 'LOGICAL|READS'
col physical_reads format b9999999 heading 'PHYSICAL|READS'
col audsid format b999999999 heading 'AUDIT|SESSION'
col program format a27 heading 'PROGRAM NAME'
col module format a16 heading 'MODULE'
col machine format a25 heading 'MACHINE'
col logon_time format a11 heading 'LOGON TIME'
rem
select s.inst_id,
       s.process,
       p.spid,
       p.pid,
       s.sid||','||s.serial# sid_serial,
       s.osuser,
       s.username,
       i.block_gets + i.consistent_gets logical,
       i.physical_reads,
       to_char( s.logon_time, 'mm/dd hh24:mi' ) logon_time,
       s.program,
       substr(s.module,1,13)||'...' module,
       s.machine
  from gv$process p, gv$session s, gv$sess_io i
 where i.sid = s.sid
   and i.inst_id = s.inst_id
   and s.inst_id = p.inst_id(+)
   and s.paddr = p.addr(+)
   and nvl(s.inst_id, 0) like nvl('&inst_id', '%')
   and nvl(s.username,'null') like nvl(upper('&user'),'%')
   and nvl(s.osuser,'null') like nvl('&osuser', '%')
   and nvl(upper(s.program),'null') like upper(nvl('&program', '%'))
   and upper(nvl(s.module,'null')) like upper(nvl('&module', '%'))
   and upper(nvl(s.machine,'null')) like upper(nvl('&machine', '%'))
   and s.sid like nvl('&sid', '%')
   and s.serial# like nvl('&serial', '%')
   and nvl(s.process,'null') like nvl('&client_pid', '%')
   and nvl(p.spid,'null') like nvl('&server_pid', '%')
 order by block_gets + consistent_gets desc, s.inst_id
/

Usage

-- Search all sessions
@gvuserall.sql
-- Enter: [press enter for all instances]
-- Enter: [press enter for all users]
-- Enter: [press enter for all OS users]
-- Enter: [press enter for all programs]
-- Enter: [press enter for all modules]
-- Enter: [press enter for all machines]
-- Enter: [press enter for all SIDs]
-- Enter: [press enter for all serials]
-- Enter: [press enter for all client PIDs]
-- Enter: [press enter for all server PIDs]

-- Search by specific user
@gvuserall.sql
-- Enter: [press enter for all instances]
-- Enter: SCOTT (for specific database user)
-- [press enter for remaining prompts]

-- Search by machine/client
@gvuserall.sql
-- Skip to machine prompt
-- Enter: WORKSTATION01 (for specific client machine)

-- Search by program
@gvuserall.sql
-- Skip to program prompt
-- Enter: sqlplus.exe (for SQL*Plus sessions)

Parameters

The script prompts for multiple filter criteria:

• inst_id: RAC instance ID (% for all instances)
• user: Database username (% for all users)
• osuser: Operating system username (% for all OS users)
• program: Client program name (% for all programs)
• module: Application module name (% for all modules)
• machine: Client machine name (% for all machines)
• sid: Session ID (% for all sessions)
• serial: Serial number (% for all serials)
• client_pid: Client process ID (% for all client PIDs)
• server_pid: Server process ID (% for all server PIDs)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on GV$PROCESS
SELECT on GV$SESSION
SELECT on GV$SESS_IO

Sample Output

                                Global User Sessions Search

INST CLIENT SERVER  PID SID,SERIAL# OS       ORACLE          LOGICAL PHYSICAL LOGON TIME  PROGRAM NAME               MODULE           MACHINE
     PID    PID          #           USERNAME USERNAME          READS    READS
---- ------ ------ ---- ----------- -------- --------------- --------- -------- ----------- -------------------------- ---------------- -------------------------
   1  15234  23456  145  1234,5678  oracle   SCOTT           2,456,789   123,456 06/01 14:30 sqlplus.exe               SQL*Plus         workstation01.company.com
   2   8765  34567  156  2345,6789  webapp   HR_USER         1,234,567    89,012 06/01 13:45 java.exe                  MyApplication... appserver02.company.com
   1  12345  45678  167  3456,7890  batch    BATCH_USER        987,654    67,890 06/01 12:00 BatchProcessor.exe        BatchProc...     batchserver03.company.com
   2   9876  56789  178  4567,8901  report   REPORT_USER       765,432    45,678 06/01 11:15 Crystal Reports          CrystalReport... reportserver04.company.com

Key Output Columns

Session Identification

• INST: RAC instance number
• SID,SERIAL#: Session identifier and serial number
• CLIENT PID: Client-side process ID
• SERVER PID: Server-side process ID

User Information

• OS USERNAME: Operating system username
• ORACLE USERNAME: Database username
• MACHINE: Client machine hostname

I/O Statistics

• LOGICAL READS: Total logical reads (block_gets + consistent_gets)
• PHYSICAL READS: Physical disk reads
• LOGON TIME: Session login timestamp

Application Details

• PROGRAM NAME: Client program/application
• MODULE: Application module (truncated to 13 characters + …)

Common Use Cases

Security Auditing

-- Find all sessions from specific machine
@gvuserall.sql
-- Filter by machine name to identify unauthorized access

Performance Investigation

-- Identify high I/O sessions
@gvuserall.sql
-- Review sessions with highest LOGICAL READS

User Activity Monitoring

-- Track specific user's sessions
@gvuserall.sql
-- Filter by username to monitor user activity

Application Analysis

-- Find sessions from specific application
@gvuserall.sql
-- Filter by program or module name

Session Cleanup

-- Identify sessions for potential termination
@gvuserall.sql
-- Use SID,SERIAL# to kill problematic sessions

RAC Load Analysis

-- Compare session distribution across instances
@gvuserall.sql
-- Analyze INST column for load balancing

Filtering Strategies

Progressive Filtering

1. Start with broad search (all parameters empty)
2. Identify patterns in results
3. Re-run with specific filters based on findings

Common Filter Combinations

-- High-activity sessions
-- Look for high LOGICAL READS values

-- Specific application troubleshooting
-- Filter by program + module combination

-- Security investigation
-- Filter by machine + osuser combination

Security Analysis

Unauthorized Access Detection

• Sessions from unexpected machines
• Unusual OS user/Oracle user combinations
• Sessions outside normal business hours

Privilege Escalation Monitoring

• OS users accessing privileged Oracle accounts
• Unexpected program/user combinations
• Sessions with high resource usage

Performance Analysis

Resource Consumption

• Sessions ordered by logical reads (highest first)
• Identify sessions consuming excessive I/O
• Correlate with physical reads for disk impact

Session Longevity

• Review LOGON TIME for long-running sessions
• Identify potentially stuck or runaway processes

Session Management

Session Termination

-- Use output to identify sessions for termination
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- For RAC environments
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;

Process Investigation

-- Use SERVER PID for OS-level investigation
-- Check process details at OS level
-- Monitor resource usage with OS tools

Performance Impact

• Moderate Impact: Joins multiple system views
• Large Result Sets: May return many rows in busy systems
• I/O Statistics: Includes additional I/O calculations

Related Scripts

• gvsess - Active session analysis
• duser - User account analysis
• dprivall - Privilege analysis