-- -----------------------------------------------------------------------------------

-- 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 PAG