As a DBA, one of the most common tasks is keeping an eye on who is connected to the database and what they’re doing. Oracle provides dynamic performance views like v$session and v$process that make this possible.

Query to list all active sessions with detailed information:

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;

What This Query Shows

• username → Database user (or (oracle) if background process)
• osuser → Operating system user running the session
• sid / serial# → Unique identifiers for the session
• spid → OS process ID associated with the session
• lockwait → If the session is waiting for a lock
• status → Session status (filtered here to ACTIVE)
• machine → Client machine name
• program → Program used to connect (SQL*Plus, TOAD, JDBC, etc.)
• logon_time → When the session logged in
• last_call_et_secs → Time since the last call in seconds
• module / action → Application module and action (useful for tracing)
• client_info / client_identifier → Extra client metadata

Meaning of last_Call_et_secs in more detail:

last_call_et is a column in v$session.

It represents the elapsed time in seconds since the last call was made by the session.

A “call” here means any database request — such as executing a SQL statement, PL/SQL block, or performing a fetch