Systems Engineering and RDBMS

Monitoring number of connections in Oracle

Posted by decipherinfosys on February 10, 2007

Here is a shell script to monitor the number of connections in the database (count, who and where).

This script takes in 3 arguments:

1) oracle_sid:  The Oracle SID
2) no. of times: Number of times you want to loop. Next argument sleep time should be considered when setting this value
3) sleep time: Number of seconds you want to sleep.

This scripts assumes that you can connect to the DB as sysdba.  If you don’t have the privilege, modify the script to connect as a different user.
/*************************************************************************/

#!/bin/ksh

if [[ “$#” -ne “3” ]]; then
print “Usage: ${0} <OracleSid> <No. of times> <sleep time>”
exit -1
fi

oracleSid=${1}
loopTimes=${2}
sleepTime=${3}

export ORACLE_SID=${oracleSid}

counter=0

if [[ ! -d “./log” ]]; then
mkdir log
else
if [[ -f ./log/session_count.log ]]; then
rm -f ./log/session_count.log
fi
fi

exec >> ./log/session_count.log 2>&1

while [[ “${counter}” -le “${loopTimes}” ]];
do
print “Connection info at: $(date)”
print
print
sqlplus -s /nolog << EOF
set feed off pages 1000 lines 120 echo off verify off
rem connecting as sysdba assuming you have logged in as Oracle
rem change this if you don’t have the privilege
rem example: connect user/password@oracle_sid
connect / as sysdba

column username heading ‘User|Name’ format a30
column machine heading ‘Machine’    format a30
column session_count heading ‘Session|Count’ format 999G999

select s.username as username,
(case
when grouping(s.machine) = 1 then
‘**** All Machines ****’
else
s.machine
end) AS machine,
count(*) as session_count
from v\$session s,
v\$process p
where s.paddr   = p.addr
and s.username is not null
group by rollup (s.username, s.machine)
order by s.username, s.machine ;
EOF

sleep ${sleepTime}
counter=$(expr ${counter} + 1)
print
print
done

exit
/*************************************************************************/

Sorry, the comment form is closed at this time.

 
%d bloggers like this: