Oracle Active Sessions Monitor
This script displays detailed information about all currently active 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 by querying Oracle dynamic performance views.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads14 views0 comments
By OracleDba • Created
Code
(41 lines)1234567891011121314151617181920212223242526272829303132333435363738394041
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/active_sessions.sql
-- Author : Tim Hall
-- Description : Displays information on all active database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @active_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'
ORDER BY s.username, s.osuser;
SET PAGESIZE 14