ksh and perl by Example: crontab task to find and kill Long Running Oracle Jobs.

General.
Every 15 minutes execute a script that would identify and kill sessions that ran longer than 30 minutes.
This script is launched on Oracle server, as oracle user.

steps
A. crontab task - that runs ksh script every 15 minutes
B. ksh script that launches perl script
C. perl script that connects to DB to find long running queries, and then kills the tasks

crontab -l
0-59/15 * * * * /backup/ora_exp/kill_long_running_jobs.sh

kill_long_running_jobs.sh
#!/bin/bash
. /etc/profile
. /etc/sh/orash/oracle_login.sh igt

perl /backup/ora_exp/kill_long_runing_jobs.pl

kill_long_runing_jobs.pl
#! /usr/bin/perl

use DBI;
use Time::gmtime;
use File::Copy;

####################################################################
#####  Subroutine to get the current date  #########################
####################################################################

sub getDate
{
  use Time::gmtime;
  $tm=gmtime;
  ($second,$minute,$hour,$day,$month,$year) = (gmtime) [0,1,2,3,4,5];
  my $locDate=sprintf("%04d%02d%02d%02d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec );
  return $locDate;
}

########################
# main start here ######
########################
my $RetCode;
my $myDate=getDate;
my $logLocation="/backup/ora_exp/";
my $logFile=$logLocation."runLog-".$myDate.".txt";
#unlink $logFile;
my @mailArg;
my $sql;
my $db="orainst";
my $db_driver="dbi:Oracle:".$db;

my $dbh=DBI->connect($db_driver,'user','password',{RaiseError =>1,AutoCommit=>0})|| die "$DBI::errstr";
print $DBI::errstr;

my $sql="select d.spid, c.serial#, a.job, a.failures, b.what, a.this_date, sysdate,a.sid, ROUND((SYSDATE - a.this_date)
* 24 * 60) from dba_jobs_running a,dba_jobs b,v\$session c,v\$process d  where a.job = b.job and a.sid = c.sid and c.pad
dr = d.addr and ((b.what in ('JOB_A.main;', 'JOB_B.main;','JOB_C.main;') and (SYSDATE - a.this_date) * 24 * 60 > 30) OR (b.what in ('JOB_A1.main;','JOB_B1.main;') AND (SYSDATE - a.this_date) * 24 * 60 > 200))";
my $FNsth=$dbh->prepare($sql);
$FNsth->execute();
my @row;
my @temp;
while (@row=$FNsth->fetchrow())
{
  open (MyLog,">>".$logFile);
  print MyLog "----I---- Startting long runing job process for ".$myDate."\n";
  print MyLog "These are the details of the job that has been running for more than 30 minutes: \n";

  print MyLog "SPID: ".$row[0]." \n";
  print MyLog "Job Num: ".$row[2]." \n";
  print MyLog "Job Description: ".$row[4]." \n";
  print MyLog "Start Time: ".$row[5]." \n";
  print MyLog "SID: ".$row[7]." \n";
  print MyLog "Minutes Running: ".$row[8]." \n";

  ##print MyLog "looking for the SPID in the OS: \n";
  #$temp = "ps -ef | grep ".$row[0]." | grep -v grep |";
  $temp = "ps -fp ".$row[0]." |";
  ##print MyLog "Command: ".$temp." \n";

  open(PS,$temp);
  $i=0;
  while (<PS>) {
     chomp;
     @psarray = split(' ',$_,8);
     $pid[$i] = $psarray[1];
     $pname[$i]=$psarray[7];
     if ($i>0) {
        print MyLog $pid[$i]."-->".$pname[$i];
        print MyLog "\n";
         ##print MyLog "TEST: ".substr($pname[$i],0,5)." \n";
         if (substr($pname[$i],0,5) eq "ora_j") {
            open(PS,"kill -9 ".$pid[$i]." |");
           print MyLog "PID #".$pid[$i]." was killed!!! \n";
         }
         else {
           print MyLog "PID #".$pid[$i]." is not an Oracle job! Process was not killed! \n";
        }
     }
     $i++;
  }

  print MyLog "----I---- Process complete. \n";

  close(PS);
}
$FNsth->finish();
$dbh->disconnect;
close MyLog;


=================================
SQL to identify long running jobs
=================================
SELECT PROCESSES.spid, 
       SESSIONS.serial#, 
      DBA_JOBS_RUNNING.job, 
      DBA_JOBS_RUNNING.failures, 
      DBA_JOBS.what, 
      DBA_JOBS_RUNNING.this_date, 
      SYSDATE,
      DBA_JOBS_RUNNING.sid, 
      ROUND((SYSDATE - DBA_JOBS_RUNNING.this_date)* 24 * 60)
  FROM DBA_JOBS_RUNNING DBA_JOBS_RUNNING,
       DBA_JOBS DBA_JOBS,
      V$SESSION SESSIONS,
      V$PROCESS PROCESSES
 WHERE DBA_JOBS_RUNNING.job = DBA_JOBS.job 
   AND DBA_JOBS_RUNNING.sid = SESSIONS.sid 
   AND SESSIONS.paddr = PROCESSES.addr 


=======================================
profile files, not really important for this example...
=======================================
. /etc/profile

# /etc/profile

# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc

pathmunge () {
        if ! echo $PATH | /bin/egrep -q "(^|:)$1($|:)" ; then
           if [ "$2" = "after" ] ; then
              PATH=$PATH:$1
           else
              PATH=$1:$PATH
           fi
        fi
}

# Path manipulation
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
pathmunge /usr/local/bin
pathmunge /usr/X11R6/bin after


# No core files by default
ulimit -S -c 0 > /dev/null 2>&1

USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"

HOSTNAME=`/bin/hostname`
HISTSIZE=1000

if [ -z "$INPUTRC" -a ! -f "$HOME/.inputrc" ]; then
    INPUTRC=/etc/inputrc
fi

export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC

for i in /etc/profile.d/*.sh ; do
    if [ -r "$i" ]; then
        . $i
    fi
done


unset i

. /etc/sh/orash/oracle_login.sh orainst
#!/bin/bash
#
# NAME:
#      oracle_login.sh
# !!!
#
# ABSTRACT:
#           Defining oracle's enironment variables. - Unix version
#
#
# ARGUMENTS:
#           [p1] - oracle sid
#
#       Note - 1) the parameter can be ommitted
#
#
# SPECIAL FILE:
#
# ENVIRONMENT VAR:
#     PROJECT_SID -
#        should be defined in the following way:
#        globaly        - the node run only one oracle instance
#        group login    - there is more than one instance on the current node
#        as a parameter - there is more than one instance per project,
#                         or for system users.
#
# SPECIAL CCC MACROS:
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
#            1) PROJECT_SID is not defined
#            2) the database PROJECT_SID does not exist
#            3) PROJECT_SID missing from $ORASH/sid.hosts
#            4) ORA_VER missing from $ORASH/sid.hosts
#            5) charcter missing for current sid from $ORASH/charset.dat
#
# LIMITATIONS:
#
# MODIFICATION HISTORY:
# +-------------+-------------------+-----------------------------------------+
# | Date        |     Name          |  Description                            |
# +-------------+-------------------+-----------------------------------------+
# +             +                   +                                         +
# +-------------+-------------------+-----------------------------------------+
if [ "$ORASH" = "" ]; then
   ORASH=$SH_ETC/etc/orash
fi

FACILITY="oracle_login:"
UNAME=`uname | cut -d_ -f1`   #=> Linux

  if [ "$#" -ge 1 ]; then
      PROJECT_SID=$1
   else
      project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
      if [ "$project_sid_def" -eq 0 ]; then
         echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
         return 1
      fi
   fi

   ORACLE_BASE=/software/oracle
   project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
   if [ "$project_sid_def" -eq 0 ]; then
         echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
         return 1
   fi

   ORACLE_HOME=`grep ^"$PROJECT_SID": /var/opt/oracle/oratab | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$ORACLE_HOME" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in /var/opt/oracle/oratab or /etc/oratab"
      return 2
   fi

   ORA_VER=`grep "$PROJECT_SID" $ORASH/sid.hosts | awk '{if (NR==1) print $0}' |cut -d: -f3`
   if [ "$ORA_VER" = "" ]; then
      echo "$FACILITY unable to verify ORA_VER for $PROJECT_SID using $ORASH/sid.hosts"
      return 4
   fi
   DEFAULT_SID=$PROJECT_SID

   ORACLE_SID=$DEFAULT_SID
   TNS_ADMIN=${ORACLE_HOME}/network/admin

#----------------------------------------------------------------#
# the hostname of the DEFAULT_SID appears first in file sid.hosts
#----------------------------------------------------------------#
   HOST=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$HOST" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $ORASH/sid.hosts"
      return 3
   fi

#-------------------------------------#
#  TWO_TASK is used for a unix clients
#-------------------------------------#
# Disable TWO_TASK feature eliminating the need to change hostname, after disk replication
#   NODE=`hostname`
#   if [ "$NODE" != "$HOST" ]; then
#      TWO_TASK=${DEFAULT_SID}_remote
#   fi

#-------------------------------------#
# setting up NLS_LANG
#-------------------------------------#
   CHARSET=`grep ^"$PROJECT_SID": $ORASH/charset.dat | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$CHARSET" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $
ORASH/charset.dat"
      return 5
   fi

   NLS_DATE_FORMAT="DD-MON-RR"
   ORACLE_LPPROG=lpr
   ORACLE_LPARGS="-P la424"
   NLS_LANG="AMERICAN_AMERICA."${CHARSET}
   ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data # for oracle
   ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data # for oracle8

   cleaned_path=$PATH
   for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
      cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
   done
   if [ $UNAME = 'SunOS' ] ; then
      cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
   done
   if [ $UNAME = 'SunOS' ] ; then
     PATH=$cleaned_path:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/openwin/bin
   else
     PATH=$cleaned_path$ORACLE_HOME/bin
   fi

   shlib_def=`set | grep "^LD_LIBRARY_PATH" |wc -l`
   if [ "$shlib_def" -eq 0 ]; then
      LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
   else
      cleaned_shlib_path=$LD_LIBRARY_PATH
      for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
         cleaned_shlib_path=`echo -e "${cleaned_shlib_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
      done

      if [ $UNAME = 'SunOS' ] ; then
         LD_LIBRARY_PATH=$cleaned_shlib_path:$ORACLE_HOME/lib:/usr/lib
      else
         LD_LIBRARY_PATH=$cleaned_shlib_path$ORACLE_HOME/lib:/usr/lib
      fi
   fi

   export PROJECT_SID
   export ORACLE_TERM
   export ORACLE_HOME
   export ORACLE_BASE
   export ORACLE_SID
   export TNS_ADMIN
   export ORACLE_SERVER
   export PATH
   export LD_LIBRARY_PATH
   export NLS_LANG
   export ORA_NLS33
   unset ORA_NLS32
   ORACLE_ENV_DEFINED="yes"
   export ORACLE_ENV_DEFINED

   export ORA_VER