1) Creating .sh file to automate the job through crontab: -
kill.sh: -
# .bash_profile
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
cd $ORACLE_HOME/kill
sqlplus system/fopsdb2008@fopsfat1 @/u01/app/oracle/product/10.2.0/db_1/kill/kill.sql
exit
2) Creating .sql file to get the locked session ID in OS level: -
kill.sql: -
set heading off
set echo off
spool /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
select 'kill -9 '|| vp.spid from v$session vs, v$process vp
where vs.paddr = vp.addr
and vs.sid in (select s1.sid from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2);
spool off
select name from v$database;
!chmod 777 /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
!export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
!cd /u01/app/oracle/product/10.2.0/db_1/kill
!pwd
!./kill_in.sh
Exit
3) Making an entry to automate this job every 1 minute: -
Crontab -e: -
* * * * * /u01/app/oracle/product/10.2.0/db_1/kill/kill.sh > /dev/null 2>&1
