Tuesday, July 28, 2009

Script to list connections to all databases on a server

#run.sh
for ORACLE_SID in $(cat /etc/oratab |grep -v ^#|grep -v ^agent | grep :Y|cut -f1 -d:)
do
ORAENV_ASK=NO
. oraenv
echo "$ORACLE_SID"
sqlplus / as sysdba @run.sql $ORACLE_SID
done

--run.sql
spool &1
set pagesize 0 linesize 200
select osuser||' '||machine||' '||program||' '||count(*) from v$session
where username <> 'SYS'
and program not like '%J00%'
group by osuser, program, machine
order by machine, osuser, program;
spool off
exit