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

2 comments:

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.