usercnt.sh

Purpose
Create a script that can be run via cron to periodically gather info about user counts. This can be useful for helping diagnose connection pools that are not releasing their connections.

Set the following variables according to your environment:
ORACLE_SID, ORACLE_HOME, PROJDIR

Note: The Backslash contained in the v$session reference is so the $ character is escaped properly. If you repurpose this script keep in mind any $ symbols in the text passed to SQLPLUS from the shell script must be escaped in the manner.

usercnt.sh

ORACLE_SID=ORCL11G; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0.1
export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc
export PATH

PROJDIR=/u01/app/oracle/usercnt
cd $PROJDIR

sqlplus -s "/ as sysdba" << ENDOFTEXT >> usercnt.csv
set feedback off heading off
set pages 0
set serveroutput on
DECLARE
  MYDATE DATE := SYSDATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('DATETIME,USERNAME,CURRENT_LOGINS');
  FOR cur IN (SELECT USERNAME, COUNT(*) as CNT FROM v\$SESSION WHERE USERNAME IS NOT NULL GROUP BY USERNAME ORDER BY 1)
  LOOP
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(MYDATE,'YYYY-MM-DD HH24:MI') || ',' || cur.USERNAME || ',' || cur.CNT);
  END LOOP;
END;
/
ENDOFTEXT

Create Cron Entry

# Usercnt Monitoring 
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /u01/app/oracle/usercnt/usercnt.sh 2>&1

Sample Output

DATETIME,USERNAME,CURRENT_LOGINS
2012-04-27 09:25,DBSNMP,5
2012-04-27 09:25,SCOTT,20
2012-04-27 09:25,SYS,1
2012-04-27 09:25,SYSTEM,1
DATETIME,USERNAME,CURRENT_LOGINS
2012-04-27 09:30,DBSNMP,5
2012-04-27 09:30,SCOTT,18
2012-04-27 09:30,SYS,1
2012-04-27 09:30,SYSTEM,1