Shell script to kill long running database sessions.

kill_long_running_sessions.sh
#!/usr/bin/ksh
##########################################################
# This script is used to find and kill the sessions are
#more than two days old.
# This script calls find_long_running_sessions.sql to
# find the sessions which are more than 2 days old
# This script calls kill_long_running_sessions.sql to
#create a sql script to kill the sessions which are
#more than 2 days old.
# $1 is the instance name in which the more than
#2 days old sessions to be killed.
# Author : Anbu S Subramanian
# Date : 05/25/2005 ###########################################################
. /oracle/.102boraenv
opwd=`cat $HOME/session_control/.infodb.$1`
export opwd
dba_team='anbus@1234.com'
dtstamp=`date +%m%d%Y`
INST_DIR=$HOME/session_control
sqlplus -s > /dev/null <
$opwd@$1
set feedback off
set echo off
set pages 50000
set lines 3000
spool $INST_DIR/find_$1_long_running_sessions_$dtstamp.log
start $INST_DIR/find_long_running_sessions.sql
spool off
set heading off
set lines 125
spool $INST_DIR/kill_$1_long_running_sessions.sql
start $INST_DIR/kill_long_running_sessions.sql
spool off
set feeback on
set echo on
set heading on
spool $INST_DIR/kill_$1_long_running_sessions.log
start $INST_DIR/kill_$1_long_running_sessions.sql
spool off
END1
/usr/bin/cat $INST_DIR/find_$1_long_running_sessions_$dtstamp.log > $INST_DIR/kill_$1_session_$dtstamp.log
/usr/bin/cat $INST_DIR/kill_$1_long_running_sessions.sql >> $INST_DIR/kill_$1_session_$dtstamp.log
/usr/bin/cat $INST_DIR/kill_$1_long_running_sessions.log >> $INST_DIR/kill_$1_session_$dtstamp.log
/usr/bin/cat $INST_DIR/kill_$1_session_$dtstamp.log grep ORA- > /dev/null 2>&1
if [ $? = 0 ]
then
mailx -s "!!!-IMPORATNT PROBLEM IN KILLING $1 SESSIONS on $dtstamp." $dba_team< $INST_DIR/kill_$1_session_$dtstamp.log
else
mailx -s "TWO DAYS OLD $1 sessions on $dtstamp are killed." $dba_team< $INST_DIR/kill_$1_session_$dtstamp.log
fi
rm $INST_DIR/find_$1_long_running_sessions_$dtstamp.log
rm $INST_DIR/kill_$1_long_running_sessions.sql
rm $INST_DIR/kill_$1_long_running_sessions.log
rm $INST_DIR/kill_$1_session_$dtstamp.log
exit
-------------------------------------------------------------------------------------------------------------------------
find_long_running_sessions.sql
rem This sql is used to find the sessions longing for more than two days.
rem This sql will be called by the unix shell script kill_long_running_sessions.sh
rem Author : Anbu S Subramanin
rem Date:05/25/2005
Select sid,serial#,username,status,osuser,program,
to_char(LOGON_TIME,'mm/dd/yyyy Hh24:mi:ss') "LOGIN DATETIME"
From v$session
Where username is not null
and program is not null
and upper(username) not in ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','MDSYS','ORDSYS',
'ORDPLUGINS','CTXSYS','DSSYS','PERFSTAT','WKPROXY','WKSYS',
'WDMSYS','XDB','ANONYMOUS','ODM','ODM_MTR','OLAPSYS',
'TRACESVR','REPADMIN','AURORA$ORB$UNAUTHENTICATED',
'AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','BIGB')
and serial#!=1
and upper(osuser)!='ORACLE'
and upper(schemaname)!='SYS'
and upper(status) in ('ACTIVE','INACTIVE','SNIPED')
and program not like 'oracle@%'
and logon_time < (sysdate -2)
order by LOGON_TIME;

------------------------------------------------------------------
kill_long_running_sessions.sql
rem This sql is used to create a sql script to kill the sessions
rem longing for more than two days.
rem This sql will be called by the unix shell script
rem kill_long_running_sessions.sh
rem Author : Anbu S Subramanin
rem Date:05/25/2005
Select 'ALTER SYSTEM KILL SESSION ' '''' sid','serial#'''' ';'
from v$session
where username is not null
and program is not null
and upper(username) not in ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','MDSYS','ORDSYS',
'ORDPLUGINS','CTXSYS','DSSYS','PERFSTAT','WKPROXY','WKSYS',
'WDMSYS','XDB','ANONYMOUS','ODM','ODM_MTR','OLAPSYS',
'TRACESVR','REPADMIN','AURORA$ORB$UNAUTHENTICATED',
'AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','BIGB')
and serial#!=1
and upper(osuser)!='ORACLE'
and upper(schemaname)!='SYS'
and upper(status) in ('ACTIVE','INACTIVE','SNIPED')
and program not like 'oracle@%'
and logon_time < (sysdate -2)
order by LOGON_TIME;