Script to find long running requests

#!/bin/ksh
echo "Program Start Date: " `date`

if [ `whoami` != 'oracle' ]
then
echo "Error: You must be oracle to execute the script. Exiting."
exit
fi


########################################################################
# Get the list of programs running for more than 4 hours
########################################################################

export ORACLE_HOME=/opt/oracle/products/9.2.0.x
export ORACLE_SID=ERPPROD
export PATH=$ORACLE_HOME/bin:$PATH

ORA_TEST=`sqlplus "/as sysdba" <set linesize 200
set pagesize 60
set feed off
set head off
SELECT 'Concurrent Program: '||t.user_concurrent_program_name||' ,With Req ID: '||r.REQUEST_ID||' ,Started at: '||to_char(v.actual_start_date,'dd/mm/yy hh24:mi:ss')||' ,With Parameters: '||v.argument_text||' ,Running for: '||round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24))||'Hours'
FROM apps.fnd_concurrent_requests r , apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl t, apps.fnd_conc_req_summary_v v
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate -1)
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.status_Code='R' and r.phase_code='R'
AND v.request_id=r.request_id
AND round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24)) > 4
order by v.actual_start_date
.
spool $ORACLE_HOME/rdbms/log/LongRunningJobs.lst
/
spool off
exit
EOF`

JobExists=`grep "Concurrent Program" $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | wc -l `

##########################################################################
# Send mail if there are programs which are running for more than 4 hours
##########################################################################


if [ $JobExists -ne 0 ]
then
cat $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | /usr/bin/mailx -s "Long Running Jobs in ERPPROD" ...
fi