Oracle User Sessions Monitor
Displays detailed information about all active user database sessions, including OS user, SID, process ID, service, machine, program, logon time, and session activity details. Useful for session monitoring and troubleshooting user activity.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads25 views0 comments
By OracleDba • Created
Code
(42 lines)123456789101112131415161718192021222324252627282930313233343536373839404142
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/user_sessions.sql
-- Author : Tim Hall
-- Description : Displays information on all user database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @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 s.username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.service_name,
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.username IS NOT NULL
ORDER BY s.username, s.osuser;
SET PAGESIZE 14