Blog

Use the script below to find out the total session count as well as the active & inactive session count in Oracle database.

- Adjust formatting
set linesize 700
set pagesize 7000

-- Enable reporting and define computations
break on report
compute SUM of 'Total' on report
compute SUM of 'Active' on report
compute SUM of 'Inactive' on report

-- Define column formatting
col username for a30

-- Retrieve session information
select DECODE(username, NULL, 'INTERNAL', USERNAME) AS Username,
       COUNT(*) AS Total,
       COUNT(DECODE(status, 'ACTIVE', STATUS)) AS Active,
       COUNT(DECODE(status, 'INACTIVE', STATUS)) AS Inactive
from gv$session
where status in ('ACTIVE', 'INACTIVE')
group by username;

EXAMPLE:

Please note that you can also use this script to find the active and inactive session count in RAC environment. If you want to check the session details then check my other article How to check session details in the Oracle database.