监控oracle+事务锁,监控数据库的常用Shell脚本-监视用户和事务（死锁等）

以下的脚本在死锁发生的时候发送一个警告e-mail：

###################################################################

## deadlock_alert.sh ##

###################################################################

#!/bin/ksh

EDITOR=vi; export EDITOR

ORACLE_SID=PPRD10; export ORACLE_SID

ORACLE_BASE=/data/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/10.2.0; export ORACLE_HOME

LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN

NLS_LANG=american; export NLS_LANG

NLS_DATE_FORMAT=’Mon DD YYYY HH24:MI:SS’; export NLS_DATE_FORMAT

ORATAB=/var/opt/oracle/oratab;export ORATAB

PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATH

DBALIST=”tianlesoftware@vip.qq.com,tianlesoftware@hotmail.com”;export DBALIST

sqlplus -s ‘/ as sysdba’ <

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

EOF

if [ `cat deadlock.alert | wc -l` -gt 0 ]

then

mailx -s “DEADLOCK ALERT for PPRD10″ $DBALIST < deadlock.alert

fi