deadlock_alert


###################################################################
 ## deadlock_alert.sh ##
 ###################################################################
 #!/bin/ksh
 . /etc/oracle.profile
 sqlplus -s <<!
 oracle/$1@$2
 set feed off
 set heading off
 spool deadlock.alert
 SELECT   SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
               DECODE(REQUEST, 0, 'NO','YES' ) WAITER
 FROM     V$LOCK 
 WHERE    REQUEST > 0 OR BLOCK > 0 
 ORDER BY block DESC; 
 spool off
 exit
 !
 if [ `cat deadlock.alert|wc -l` -gt 0 ]
 then
     mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alert
 fi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: