Oracle Database / Connections & Oracle Net
Detect excessive Oracle application sessions from one client group
A connection pool leak can slowly consume available sessions even though most sessions are idle. Group sessions by service and application identity.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT service_name, machine, program, status, COUNT(*) AS sessions FROM v$session WHERE type = 'USER' GROUP BY service_name, machine, program, status ORDER BY sessions DESC;Read the result
Large counts identify where to investigate, not whether every connection is leaked. Compare the snapshot with configured pool sizes and the number of application replicas.
Resolve and verify
Trend the group during normal and failed requests. Fix connection-close and transaction-handling paths in the application, then right-size the pool and verify recovery under load.
Operational precautions
Do not kill all inactive sessions: they may hold transactions or be valid pooled connections. Use GV$SESSION for a complete RAC-wide investigation.