Find Oracle inactive session details: Easy Guide

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

Oracle Inactive 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='INACTIVE'
ORDER  BY 1, 2, 7;

Sample Output:

Here is what the query output might look like:

Find Oracle inactive session details: Easy Guide

Monitoring inactive sessions helps free up resources that are being consumed unnecessarily. It also helps to identify and remove sessions that are no longer in use but are still holding system resources.

For more information on Oracle session monitoring, visit the Oracle Documentation on Session Management.

👉 Check out this guide on Monitoring Oracle Active and Inactive Sessions to enhance your database performance.