SHELL SCRIPT TO KILL SESSIONS SPECIFIC TO WAIT EVENTS IN ORACLE

Use this script to kill sessions which wait on some event. Use it at your own risk. Test it thoroughly before use as it is not foolproof.

[oracle@xhydra ~]$ cat Tcpwkill.sh
#!/bin/bash



trap '/home/oracle/TSKGAS_KILL.sql ' EXIT
#For logging purpose
_LOG_0()
{
echo "*************************************$1"
}

#Set the environment variables
_SET_ENV_1()
{
export ORACLE_SID=db9zx
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:/usr/sbin:/usr/local/bin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
export LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib:/lib:/usr/lib
}

_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"
_TCP_KILL_SCRIPT_3
_KILL_SESSION_4

fi
}
_TCP_KILL_SCRIPT_3()
{
LOGFILE=$(date +"%d-%m-%Y")_log_for_reference
date >> $LOGFILE
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF >> $LOGFILE
spool TSKGAS_KILL.sql
set heading off
set feedback off
select 'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||''' IMMEDIATE;'
from v\$session s
where event like '%TCP Socket (KGAS)';
spool off
EOF
TCPKILL=/home/oracle/TSKGAS_KILL.sql >/dev/null 2>&1
chmod -R 775 $TCPKILL
}



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

Leave a Reply