Oracle session details for active and inactive sessions

The Oracle session details script is a valuable tool for database administrators to monitor session activity, including active, inactive, and killed sessions. This script helps you gain insights into the status of Oracle sessions, allowing for quick troubleshooting and optimization.

SQL Session Status Query Script:

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 IN ('ACTIVE', 'INACTIVE', 'KILLED') 
ORDER  BY 1, 2, 7;

Sample Output:

Here’s an example of what you can expect when running the above script:

Oracle session details for active and inactive sessions

This Oracle session details query will help you monitor the current activity in the database, identify idle sessions that may require attention, and ensure that terminated sessions are released properly.

For more information on managing Oracle database sessions, visit the official Oracle Database Documentation.

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