Kill long running forms / inactive forms processes

kill_forms_db_long.txt

#!/bin/ksh
# ============================================================================
# Program kill_forms_db_long.ksh - Kill long running/inactive forms processes
# History:
# Date Who Description
# -------- ------------- -----------------------------------
# 04/09/07 Navdeep Saini Initial Creation
# 04/20/07 Navdeep Saini Hard coded the forms: INVTTMTX, INVTVQOH, INVTOTRX
# Usage:
# kill_forms_db_long.ksh [SID]
# ============================================================================
##############################################################
# userror: echos the usage error line and exits with an exit code of 1
#############################################################
# Set environmental variables
#
command=$(print $0 | awk -F/ '{print $NF }')
typeset -xu ORACLE_SID
LOG=${HOME}/local/log/kill_forms_db_long.log
PW=/usr/local/bin/GetPass.ksh
typeset -x HOSTNAME=$(hostname)
typeset -x DATE=$DATE
function userror
{
print "Usage: $command []"
exit 2
}
#############################################################
# check that applmgr is running this script
#
if [[ $(/usr/bin/whoami) != applmgr ]]; then
echo "You must be applmgr to run this script."
exit 1
fi
############################################################
# if there are no command line parameters the script is in error
#
if [[ $# = 0 ]]; then
print "Please enter the proper number of command line parameters"
userror
fi
ORAENV_ASK=NO
typeset -x ORACLE_SID=$1
. /usr/local/bin/db
. ${PW} system
# Check to see if the SYSTEM password is available
file:///C|/blog/kill_forms_db_long.txt (1 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
if [[ -z ${SYSTEM_PASS} ]]; then
print "${PW} does not have the SYSTEM password for $ORACLE_SID"
userror
fi

#############################################################
#Main
#############################################################
sqlplus -s <system/${SYSTEM_PASS}@${ORACLE_SID}
set lines 200
set head off
column machine format a10
column logon_time format a19
column spid format a10
column process format a10
column module format a18
spool /tmp/kill_forms_db.lst
select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
select 'Following user forms session, inactive for more than 60 min, are killed' from dual;
set head on
set pagesize 1000
SELECT
p.spid,
s.process,
s.status,
s.machine,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et/3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE' order by logon_time;
spool off
file:///C|/blog/kill_forms_db_long.txt (2 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
set head off
set feedback off
spool /tmp/db_sessions.lst
Select
'alter system kill session '''||s.sid||','||s.serial#||''';'
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE';
spool off
spool /tmp/forms_session.sh
SELECT
'remsh '||s.machine||' kill -7 '||s.process
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE';
spool off
EOF

#cat /tmp/kill_forms_db.lst |awk '{print "alter system kill session ('" $7 "\,'" $8}'
#mail -s "Long running forms sessions in "$HOSTNAME navdeep.saini@echostar.com < /tmp/kill_forms_db.lst

cat /tmp/kill_forms_db.lst >> $LOG

file:///C|/blog/kill_forms_db_long.txt (3 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
cat /tmp/db_sessions.lst >> $LOG
cat /tmp/forms_session.sh >> $LOG
#Check to see if any rows returned and run the kill db and forms
NUMROWS=`cat /tmp/kill_forms_db.lst|grep "no rows selected"`
#echo $NUMROWS
if [[ $NUMROWS != "no rows selected" ]] then
sqlplus -s <system/${SYSTEM_PASS}@${ORACLE_SID}
@/tmp/db_sessions.lst
EOF
#echo "inside if condition"
sh /tmp/forms_session.sh
cat /tmp/kill_forms_db.lst | mailx -s "Ran kill long running forms in "$ORACLE_SID DBATeam@yourdomain.com
fi