/********* session_info.ksh ****************/
#!/bin/ksh
. /home/oracle/oraprocs/WEBP18F.env
USR_ID=vivek
USR_PASS=viv1ek
database=WEBP18F
a=`ps -ef|grep “session_info.ksh”|grep -v grep|wc -l`
if [ $a -gt 4 ]
then
echo “Exiting because of more than 1 process is running `date`”>>/db3/oradata/log/session_info.log
exit
fi
echo “—–Begin —– Date `date`——————————–” >> /db3/oradata/log/session_info.log
$ORACLE_HOME/bin/sqlplus -s <
$USR_ID/$USR_PASS@$database
set serverout on
set feedback off
SET PAGES 0
start /home/oracle/scripts/sess_server1.sql
EOF
/home/oracle/scripts/find_proc_mem_mts.sh >> /db3/oradata/log/session_info.log
free -t >>/db3/oradata/log/session_info.log
echo “—–End —– Date `date`——————————–” >> /db3/oradata/log/session_info.log
exit
/************ sess_server1.sql ***************/
column osuser format a10
column username format a30
column machine format a30
column program format a40
column sid format 99999999
column serial# format 99999999
column TOT format 999999 heading “Total Sessions”
break on report
compute sum of TOT on report
set lines 141
set pages 500
select machine, osuser, username, count(1) “TOT” from v$session
group by machine, osuser, username;
select username, status, count(1) “TOT” from v$session
group by username,status;
prompt ‘APPSERVER INACTIVE CONNECTIONS’
select username, status, count(1) “TOT” from v$session
where username like ‘APPSERVER%’ and status=’INACTIVE’
group by username,status
order by status, username;
prompt ‘APPSERVER ACTIVE CONNECTIONS’
select username, status, count(1) “TOT” from v$session
where username like ‘APPSERVER%’ and status=’ACTIVE’
group by username,status
order by status, username;
prompt ‘Total Server Processes’
!ps -ef | grep -v grep | grep WEBP18F |wc -l