SQL Query to Check User-wise INACTIVE Session Count in Oracle

Effectively managing database sessions is crucial for optimal performance. This SQL query identifies active and inactive sessions for each user in an Oracle database, offering a user-wise breakdown of session counts.

SET LINESIZE 150
SET PAGESIZE 50
COL STATUS FORMAT A15
COL COUNT FORMAT 99999
COL USERNAME FORMAT A25
SELECT NVL(s.username, 'UNKNOWN') AS USERNAME, s.status, COUNT(1) AS SESSION_COUNT 
FROM v$process p, v$session s
WHERE paddr(+) = addr
GROUP BY NVL(s.username, 'UNKNOWN'), s.status
ORDER BY s.status;

Example Output

USERNAME                  STATUS          SESSION_COUNT
------------------------- --------------- -------------
SYS                       ACTIVE                      1
UNKNOWN                   ACTIVE                     56
UNKNOWN                                              19
W3BUDDY                   INACTIVE                  120

Explanation of UNKNOWN

The NVL function replaces NULL values in the USERNAME column with UNKNOWN, representing sessions not tied to a specific user, such as background processes or unidentified system operations.

Conclusion

This query offers a clear view of database sessions, enabling administrators to identify inactive sessions and optimize resources efficiently.