#!/bin/bash
. /home/oracle/.bash_profile
ospid=`sqlplus -s / as sysdba<<EOF
set linesize 1000 pagesize 0 feedback off trimspool on
select spid from (select distinct blocking_session,event from v\\\$session where username='DBMON' and blocking_session is not null) s,v\\\$process p,v\\\$session a where a.paddr=p.addr and s.blocking_session=a.sid and a.SECONDS_IN_WAIT>10 and s.event='enq: TX - row lock contention';
EOF`
sqlplus -s / as sysdba>>session_kill_info<<!
set linesize 1000 pagesize 0 feedback off trimspool on
set linesize 200
set pagesize 200
set colsep '|'
column sid format 9999999
column command format a20
column program format a25
column username format a15
column machine format a15
column event format a25
column sql_text format a40
column name format a30
column member format a30
column type format a20
column value format a35
undef v_sid
col sid format 99999 heading "SID"
col username format a8
col program format a19 heading "Program"
col machine format a10 heading "Machine"
col status format a6 heading "Status"
col event format a18 heading "Event"
col seq# format 99999 heading "Wait|Seq#"
col p1 format 99999999999 heading "Wait|P1"
col p2 format 999999999 heading "Wait|P2"
col p3 format 999999999 heading "Wait|P3"
col wait_class format a14 heading "Wait|Class"
col wait_time format 9999 heading "Wait|Time"
col seconds_in_wait format 999999 heading "Seconds|In Wait"
col sql_id format 999 heading "SQL|ID"
col blocking_sess format a10 heading "Blocking|Session"
TTITLE "execute kill session time"
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TTITLE "execute kill session information"
select s.sid,s.username, substr( s.program, 1, 19 ) program,
substr( s.machine, 1, 10 ) machine,
decode( s.status, 'ACTIVE', 'ACTIVE', 'INACTIVE', 'INACT' ) status,
s.wait_time, substr( s.event, 1, 18 ) event, s.wait_class, s.seq#,
s.p1, s.p2, s.p3, s.seconds_in_wait seconds_in_wait, s.sql_id,
s.blocking_instance || ':' || s.blocking_session blocking_sess
from v\$session s,(select distinct blocking_session,event from v\$session where username='DBMON' and blocking_session is not null) s1
where s1.blocking_session=s.sid and s1.event='enq: TX - row lock contention';
!
for i in $ospid
do
kill -9 $i
done
