All User Sessions Search (vuserall.sql)

What This Script Does

This script provides comprehensive session search capabilities with multiple filter criteria including username, OS user, program, module, machine, SID, serial number, and process IDs. Results are ordered by logical reads to show the most I/O intensive sessions first.

The Script

rem  vuserall.sql
rem
set linesize 180
rem
ttitle 'Global User Sessions Search'
rem
clear break
rem
col pid format 999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a9 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 a13 heading 'MODULE'
col machine format a25 heading 'MACHINE'
col logon_time format a11 heading 'LOGON TIME'
rem
select
       s.process,
       p.spid,
       p.pid,
       s.sid,
       s.serial#,
       s.osuser,
       s.username,
       i.block_gets + i.consistent_gets logical,
       i.physical_reads,
       -- s.audsid,
       to_char( s.logon_time, 'mm/dd hh24:mi' ) logon_time,
       s.program,
       s.module,
       s.machine
  from v$process p, v$session s, v$sess_io i
 where i.sid = s.sid
   and s.paddr = p.addr(+)
   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;
rem
rem set linesize 80

Parameters

The script prompts for:

user: Oracle username filter (optional, defaults to all users)
osuser: OS username filter (optional, defaults to all OS users)
program: Program name filter (optional, defaults to all programs)
module: Module name filter (optional, defaults to all modules)
machine: Machine name filter (optional, defaults to all machines)
sid: Session ID filter (optional, defaults to all sessions)
serial: Serial number filter (optional, defaults to all serials)
client_pid: Client process ID filter (optional, defaults to all client PIDs)
server_pid: Server process ID filter (optional, defaults to all server PIDs)

Usage

-- Basic usage (will prompt for all filters)
@vuserall.sql

-- Example values when prompted (leave blank for no filter):
-- user: SCOTT
-- osuser: oracle
-- program: sqlplus%
-- module:
-- machine: db01%
-- sid:
-- serial:
-- client_pid:
-- server_pid:

Sample Output

                               Global User Sessions Search

CLIENT SERVER                 ORACLE     LOGICAL  PHYSICAL LOGON TIME  PROGRAM NAME                MODULE        MACHINE
PID    PID    PID  SID SERIAL OS       USERNAME     READS     READS
------ ------ --- ---- ------ -------- --------- --------- --------- ----------- --------------------------- ------------- -------------------------
12345  5678   123  145   1234 oracle   SCOTT      8,456,789   234,567 01/05 14:30 sqlplus@db01 (TNS V1-V3)   SQL*Plus      db01.company.com
12346  5679   124  146   2345 oracle   JOHN       5,234,567   123,456 01/05 14:25 TOAD.exe                    Toad          workstation01.company.com
12347  5680   125  147   3456 oracle   MARY       3,789,123    98,765 01/05 14:20 SQL Developer               SQL Developer workstation02.company.com
12348  5681   126  148   4567 oracle   ALICE      2,567,890    67,890 01/05 14:15 sqlplus@db01 (TNS V1-V3)   SQL*Plus      db01.company.com