Blog

Use the script below to find the active and inactive session details in Oracle database. It script can be used in the Oracle RAC database also.

-- Adjust output formatting
SET LINESIZE 700 PAGESIZE 7000

-- Define column formats
COLUMN box FORMAT A10
COLUMN spid FORMAT A06
COLUMN username FORMAT A10
COLUMN program FORMAT A20
COLUMN os_user FORMAT A10
COLUMN LOGON_TIME FOR A20  

-- Retrieve session information
SELECT
    b.inst_id,
    b.sid,
    b.serial#,
    a.spid,
    SUBSTR(b.machine, 1, 30) AS box,
    TO_CHAR(b.logon_time, 'dd-mon-yyyy hh24:mi:ss') AS logon_time,
    SUBSTR(b.username, 1, 30) AS username,
    SUBSTR(b.osuser, 1, 20) AS os_user,
    SUBSTR(b.program, 1, 30) AS program,
    b.status,
    b.last_call_et AS last_call_et_secs,
    b.sql_id 
FROM
    gv$session b
    JOIN gv$process a ON b.paddr = a.addr AND a.inst_id = b.inst_id
WHERE
    b.type = 'USER'  
ORDER BY
    b.inst_id, b.sid;

OUTPUT EXAMPLE:

Please note that last_call_et_secs is the elapsed time since the last call(script execution). If you just want the session count instead of session details in Oracle then follow my other article Check session count in Oracle Database.