Find Oracle active session details: 1 Easy Guide

Post published:February 17, 2025

Use this Oracle active session details script to monitor and troubleshoot inactive database sessions, helping improve database performance and resolve session issues.

Oracle Active Session Query:

SET LINES 300 PAGES 1500
COLUMN pu FORMAT a12 HEADING 'OS|Login ID'
COLUMN su FORMAT a12 HEADING 'Oracle|User ID'
COLUMN sstat FORMAT a10 HEADING 'Session|Status'
COLUMN ssid FORMAT 999999 HEADING 'Oracle|Session ID'
COLUMN sser FORMAT 999999 HEADING 'Oracle|Serial No'
COLUMN spid FORMAT 999999 HEADING 'OS|Process ID'
COLUMN txt FORMAT a60 HEADING 'Current/Previous SQL Statement'

SELECT proc.username pu, 
       sess.username su, 
       sess.status sstat, 
       sess.sid ssid, 
       sess.serial# sser, 
       LPAD(proc.spid, 7) spid, 
       SUBSTR(sqla.sql_text, 1, 540) txt
FROM   v$process proc, 
       v$session sess, 
       v$sqlarea sqla 
WHERE  proc.addr = sess.paddr 
       AND sess.username IS NOT NULL 
       AND sess.sql_address = sqla.address(+) 
       AND sess.sql_hash_value = sqla.hash_value(+) 
       AND sess.status='ACTIVE'
ORDER  BY 1, 2, 7;