Alert if the db sessions exceed the threshold.

Session_Trouble.sh
#!/usr/bin/ksh
#####################################################
### This script is used to monitor the database sessions for the applications.
### $1 is the instance name.
### Author : Anbu S Subramanian
###Date : 03/18/2008
#####################################################
. /oracle/.102boraenv
INST_DIR=$HOME/alert_max_sessions
opwd=`cat $INST_DIR/.infodb.pw`
export opwd
dba_team='anbus@123.com'
dtstamp=`date +%m-%d-%Y:%H:%M`
sqlplus -s > /dev/null <
$opwd@$1
set feedback on
set echo on
set pages 10000
set lines 100
spool $INST_DIR/session_trouble_$1_$dtstamp.log
start $INST_DIR/session_trouble.sql
spool off
END1
/usr/bin/cat $INST_DIR/session_trouble_$1_$dtstamp.log grep WARNING > /dev/null 2>&1
if [ $? = 0 ]
then
mailx -s "WARNING!!!!! SESSIONS EXCEEDED THRESHOLD IN $1 on $dtstamp." $dba_team< $INST_DIR/session_trouble_$1_$dtstamp.log
###else
###mailx -s "COOL SESSIONS UNDER CONTROL IN $1 ON $dtstamp" $dba_team< $INST_DIR/session_trouble_$1_$dtstamp.log
fi
rm $INST_DIR/session_trouble_$1_$dtstamp.log
exit

session_trouble.sql

set serveroutput on size 1000000
set feedback off
set echo off
set pages 1000
set line 125
DECLARE
Cursor find_sess_cur is
SELECT DISTINCT
username,
count(username)
FROM v$session
WHERE username in ('aaaa','bbbb','cccc','dddd')
GROUP BY username
ORDER BY username;
v_find_sess_cur find_sess_cur%rowtype;
v_count integer := 0;
v_count1 integer := 0;
v_sqlerrm varchar2(200):= null;
v_sqlcode number := 0;
v_instance varchar2(30);
v_date_time varchar2(30);
v_username varchar2(30);
BEGIN
IF find_sess_cur%isopen
then
close find_sess_cur;
END IF ;
SELECT upper(instance_name) into v_instance from v$instance;
SELECT to_char(sysdate,'MM/DD/YYYY:HH24:MI') into v_date_time from dual;
SELECT MAX(COUNT(username)) INTO v_count
FROM v$session
WHERE username in ('aaaa','bbbb','cccc','dddd')
GROUP BY username;
IF v_count > 30
THEN
dbms_output.put_line('!!!!WARNING DB CONNECTIONS EXCEEDED THRESHOLD IN 'v_instance ' ON 'v_date_time);
OPEN find_sess_cur;
LOOP
FETCH find_sess_cur into v_find_sess_cur;
EXIT when find_sess_cur%notfound;
v_username:=v_find_sess_cur.username;
SELECT count(username) into v_count1 FROM v$session
WHERE username is not null
AND username=v_username
GROUP BY username;
IF v_count1 > 30
THEN
dbms_output.put_line(v_username ' has 'v_count1' db connections in ' v_instance' ON 'v_date_time);
END IF;
END LOOP;
CLOSE find_sess_cur;
ELSE
dbms_output.put_line('THE CONNECTIONS ARE OK IN ' v_instance);
END IF;
EXCEPTION
WHEN OTHERS THEN
V_SQLERRM := SUBSTR(SQLERRM,1,200);
V_SQLCODE := SQLCODE;
DBMS_OUTPUT.PUT_LINE ('ERROR 'V_SQLERRM V_SQLCODE);
END;
/