Oracle Active User Sessions Monitor

This script displays detailed information about all active user database sessions. It retrieves session and process details such as username, OS user, session ID, process ID, machine, program, logon time, module, and client information. Unlike the general active sessions script, this version filters out background Oracle sessions by including only sessions with a valid username.

oraclesqlmonitoring-alertsv1.0.0

0 stars0 downloads13 views0 comments

By OracleDba • Created 2/23/2026

Code

(42 lines)

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/active_user_sessions.sql
-- Author       : Tim Hall
-- Description  : Displays information on all active database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @active_user_sessions
-- Last Modified: 16-MAY-2019
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A30
COLUMN osuser FORMAT A20
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A45
COLUMN machine FORMAT A30
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
       s.last_call_et AS last_call_et_secs,
       s.module,
       s.action,
       s.client_info,
       s.client_identifier
FROM   v$session s,
       v$process p
WHERE  s.paddr  = p.addr
AND    s.status = 'ACTIVE'
AND    s.username IS NOT NULL
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

Oracle Active User Sessions Monitor

Code

General Comments(0)