Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to kill blocked sessions automatically in Oracle 11g database?

user-image
Question added by Kamran Saeed Kamran , Oracle Apps DBA , DHA Lahore
Date Posted: 2015/03/31
Deleted user
by Deleted user

I think you can create script for searching and killing blocked sessions in oracle, set this script to crontab, as example at Linux systems:

* * * * * /home/oracle/scripts/kill_blocked.sh, which will run every minutes.

Script like  this:

# -- bash script --

ORACLE_SID=$1; export ORACLE_SID

sqlplus -s -l / as sysdba <<!   

set linesize150   

set heading off   

set feedback off   

spool /tmp/kill_blocked.log   

select p.spid from v$session s, v$process p where s.paddr = p.addr and s.blocking_session is not null;   

spool off   

exit;

!

log=/tmp/kill_blocked.log

if [ `cat $log|wc -l` -gt0 ]; then

while read line; do

kill -9 $line

done < $log 

Kamran Saeed Kamran
by Kamran Saeed Kamran , Oracle Apps DBA , DHA Lahore

Thanks for your answer but i am not using crontab for this purpose

Can you give me some example of  Schedule Job execution

More Questions Like This