Below mentioned is a ready to use Linux script to check whether any Oracle database jobs are in broken state or if a job is taking too long to execute . The script uses sqlplus command and refers user_jobs table to capture the details . Copy the code below highlighted in blue , save it in a file and execute for output. There is a variable TIME_DIFFERENCE provided for customization. Configure the time in minutes since when the job is still running here. For example if 120 , if any job is still under execution past 120 minutes then alerts.
Change the oracle user name and password in the sqlplus connection string as required.
#!/bin/sh
TIME_DIFFERENCE=120
sqlplus -silent sys/oracle@dbname << EOF > /tmp/OraJobCheck_result.txt
set feed off echo off pagesize 0
select '('||job||')'||what||','||broken||','||last_date from user_jobs where to_number(to_number(datediff('MI',LAST_DATE,sysdate)) - to_number(datediff('MI',LAST_DATE,NEXT_DATE))) > $TIME_DIFFERENCE or BROKEN='Y';
EOF
ErrJobcount=`cat /tmp/OraJobCheck_result.txt |wc -l`
if [ $ErrJobcount -lt 1 ]
then
echo -e "\033[1;32mAll scheduled DB jobs are OK.\033[0m"
else
echo -e "\033[1;31m`cat /tmp/OraJobCheck_result.txt`\033[0m"
fi
Hope it helps to some Linux beginners.