Oracle Sessions by Machine Report
Displays the number of database sessions grouped by client machine, categorized by session status. Useful for identifying connection distribution and monitoring activity from different client hosts.
oraclesqlmonitoring-alertsv1.0.1
0 stars0 downloads4 views0 comments
By OracleDba • Created
Code
(21 lines)123456789101112131415161718192021
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/sessions_by_machine.sql
-- Author : Tim Hall
-- Description : Displays the number of sessions for each client machine.
-- Requirements : Access to the V$ views.
-- Call Syntax : @sessions_by_machine
-- Last Modified: 20-JUL-2014
-- -----------------------------------------------------------------------------------
SET PAGESIZE 1000
SELECT machine,
NVL(active_count, 0) AS active,
NVL(inactive_count, 0) AS inactive,
NVL(killed_count, 0) AS killed
FROM (SELECT machine, status, count(*) AS quantity
FROM v$session
GROUP BY machine, status)
PIVOT (SUM(quantity) AS count FOR (status) IN ('ACTIVE' AS active, 'INACTIVE' AS inactive, 'KILLED' AS killed))
ORDER BY machine;
SET PAGESIZE 14