-- Active Sessions (in ASH) including BACKGROUND processes in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time FORMAT a19
COLUMN stat_name   FORMAT a15

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ash AS
(
  SELECT TRUNC(sample_time, 'mi') sample_time
         -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi') sample_time
       , 'Active Sessions' stat_name
       , ROUND(COUNT(*)/6e1, 2) stat_value
  FROM v$active_session_history
  WHERE session_type IN ('BACKGROUND', 'FOREGROUND')  -- including background processes
  GROUP BY TRUNC(sample_time, 'mi')
           -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi')
  ORDER BY sample_time
)
SELECT * FROM ash
WHERE sample_time >= SYSDATE - INTERVAL '60' MINUTE
;

-- Active Sessions (in ASH) excluding BACKGROUND processes in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time FORMAT a19
COLUMN stat_name   FORMAT a15

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ash AS
(
  SELECT TRUNC(sample_time, 'mi') sample_time
         -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi') sample_time
       , 'Active Sessions' stat_name
       , ROUND(COUNT(*)/6e1, 2) stat_value
  FROM v$active_session_history
  WHERE session_type = 'FOREGROUND'  -- excluding background processes
  GROUP BY TRUNC(sample_time, 'mi')
           -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi')
  ORDER BY sample_time
)
SELECT * FROM ash
WHERE sample_time >= SYSDATE - INTERVAL '60' MINUTE
;

SQL> select distinct type from v$session;

TYPE
--------------------
USER
BACKGROUND

SQL> select count(*) from v$session where status = 'ACTIVE' and type = 'USER';

COUNT(*)
-----------
                2