Skip to content

Program Connection Count by Username (pgmcntu.sql)

The Script

rem  pgmcntu.sql
rem
ttitle 'Program Connection Count By Username'
rem
set lines 200
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'
rem
select inst_id
     , username
     , program
     , machine
     , count(*) cnt
  from gv$session s
 where upper(s.username) like nvl( upper('&username'), '%')
   and (program not like 'oracle%' or program is null)
group by inst_id, username, program, machine
order by username, inst_id, program, cnt desc
/

Parameters

The script prompts for:

username: Username pattern (% for all users)

pgmcntu2 - Enhanced version with service names