定时查杀行锁脚本

#!/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