kill blocking session script
Hi, I have a script I wrote to kill blocking sessions.
kill_bill.sql
select 'alter system kill session '''||v.sid||','||s.serial#||''';' from v$lock v, v$session s
where v.sid = s.sid
and v.block = 1;
It generates the write syntax. Then I wrote a shell script to executed through cron.
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set echo off
set timing off
set verify off
set feedback off
set head off
spool /home/svc_oracle/scripts/DBA/kb.sql
@$SCRIPT_HOME/kill_bill.sql
spool off
spool /home/svc_oracle/scripts/DBA/kill_session.log
kill_bill.sql
select 'alter system kill session '''||v.sid||','||s.serial#||''';' from v$lock v, v$session s
where v.sid = s.sid
and v.block = 1;
It generates the write syntax. Then I wrote a shell script to executed through cron.
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set echo off
set timing off
set verify off
set feedback off
set head off
spool /home/svc_oracle/scripts/DBA/kb.sql
@$SCRIPT_HOME/kill_bill.sql
spool off
spool /home/svc_oracle/scripts/DBA/kill_session.log
0