Enhanced Program Connection Count by Username (pgmcntu2.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

Enhanced version that includes service name information for comprehensive connection analysis.

The Script

rem  pgmcntu2.sql
rem
ttitle 'Program Connection Count By Username'
rem
set lines 250
set pagesize 150
rem
col inst_id format 99 heading 'RAC|INST'
col username format a30 heading 'Username'
col program format a48 heading 'PROGRAM NAME'
col cnt format 999,999 heading 'Count'
col machine format a50 heading 'Machine'
col service_name format a50 heading 'Service'
rem
select inst_id
     , username
     , program
     , machine
     , service_name
     , count(*) cnt
  from gv$session s
 where upper(s.username) like nvl( upper('&username'), '%')
   and (program not like 'oracle%' or program is null or program = '')
group by inst_id, username, program, machine, service_name
order by username, inst_id, program, cnt desc
/

Parameters

The script prompts for:

• username: Username pattern (% for all users)