SHELL SCRIPT TO KILL STATISTICS JOB IF IT EXCEEDS A TIME AUTOMATICALLY ORACLE

Use this script to kill the statistics running sessions automatically by setting the script in crontab during the scheduled window

#!/bin/bash

trap 'rm -rf /home/oracle/oratab_new /home/oracle/STAT_KILL.sql' EXIT

#For logging purpose
_LOG_0()
{
echo "*************************************$1"
}

#Set the environment variables
_SET_ENV_1()
{
cat /etc/oratab|grep -v '#'|grep -v '^$' > /home/oracle/oratab_new
while read x
   do
     IFS=':' read -r -a array <<< $x
                ORACLE_SID="${array[0]}"
                ORACLE_HOME="${array[1]}"
                _LOG_0
                echo "ENVIRONMENT VARIABLES SET"
                _LOG_0
                echo $ORACLE_SID
                echo $ORACLE_HOME
                export PATH=$PATH:$ORACLE_HOME/bin
   done < /home/oracle/oratab_new
}

_CHECK_DB_STATUS_2()
{
exit|sqlplus sys/password|grep 'ORA-01034'
if [ $? -eq 0 ]
 then
     echo "DB check failed:$ORACLE_SID is down.Start the database and retry"
 else
     _LOG_0
     echo "DB check passed:$ORACLE_SID is up"
_STAT_KILL_SCRIPT_3
_KILL_SESSION_4

fi
}
_STAT_KILL_SCRIPT_3()
{
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF >> log_for_reference
spool STAT_KILL.sql
set heading off
set feedback off
col sid for 999999
col serial# for 999999
col elapsed_seconds for 999999
select 'ALTER SYSTEM KILL SESSION '''||sl.sid||','||sl.serial#||''' IMMEDIATE;'
from v\$session_longops sl
inner join v\$session se on (sl.sid = se.sid)
and
(sl.serial# = se.serial#)
where sl.message like '%Gather%Stat%'
  and
se.status = 'ACTIVE'
  and
sl.elapsed_seconds > 5;
spool off
EOF
STATKILL=/home/oracle/STAT_KILL.sql
chmod -R 775 $STATKILL
}



_KILL_SESSION_4()
{
while true
   do
    if [ -s $STATKILL ]
    then
        _LOG_0
        echo "STATS session is killed"
        _LOG_0
        exit|$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' @$STATKILL
    break
    else
        _LOG_0
        echo "No process to kill"
        _LOG_0
    break
    fi
   done
}
_SET_ENV_1
_CHECK_DB_STATUS_2

Just for testing purpose i executed a stats job and set the script in crontab for every second. The job got failed due to kill script after 5 seconds

SQL> exec dbms_stats.gather_database_stats();
BEGIN dbms_stats.gather_database_stats(); END;

*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 168
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14424
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11319
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11428
ORA-06512: at "SYS.DBMS_STATS", line 34983
ORA-06512: at "SYS.DBMS_STATS", line 36699
ORA-06512: at "SYS.DBMS_STATS", line 38023
ORA-06512: at "SYS.DBMS_STATS", line 38642
ORA-06512: at "SYS.DBMS_STATS", line 39786
ORA-06512: at "SYS.DBMS_STATS", line 41177
ORA-06512: at "SYS.DBMS_STATS", line 41978
ORA-06512: at "SYS.DBMS_STATS", line 42526
ORA-06512: at "SYS.DBMS_STATS", line 42597
ORA-06512: at line 1

Leave a Reply