Lock Block

LOCKBLOCK Author JP Vijaykumar Oracle DBA Date May 31st 2009 This script finds locks held in exclusive mode for more than five minutes and blocking other sessions. set head on set linesize 200 pagesize 1000 echo on feedback on column sid format 999 column serial# format 999999 column username format a8 column osuser format a8 column type format a2 heading "TY|PE" column type_desc format a12 column id1 format 9999999999 column id2 format 9999999999 column lmode format 9 heading "LM|OD|E" column request format 9 heading "RE|QU|ES|T" column block format 99 heading "BL|OC|K" column ctime format 999999 column seconds_in_wait format 999999 heading "SECONDS|IN|WAIT" column blocking_session format 9999 heading "BLO|CK|ING|SESS|ION" column blocking_session_status format a12 heading "BLOCKING|SESSION|STATUS" column object_name format a15 heading "LOCKED|OBJECT|NAME" column lock_status format a9 heading "LOCK|STATUS" column spid format a8 heading "DB|PID" column pid format 999999999 heading "ORACLE|PID" column process heading "APPLN|PID" select decode(lmode,6,'HOLDER ',' WAITER') lock_status, l.sid,s.serial#,s.username,s.osuser,l.type, /* DECODE(l.type, 'BL','Buffer Cache Management (PCM lock)', 'CF','Controlfile Transaction', 'CI','Cross Instance Call', 'CU','Bind Enqueue', 'DF','Data File', 'DL','Direct Loader', 'DM','Database Mount', 'DR','Distributed Recovery', 'DX','Distributed Transaction', 'FS','File Set', 'IN','Instance Number', 'IR','Instance Recovery', 'IS','Instance State', 'IV','Library Cache Invalidation', 'JQ','Job Queue', 'KK','Redo Log Kick', 'LA','Library Cache Lock', 'LB','Library Cache Lock', 'LC','Library Cache Lock', 'LD','Library Cache Lock', 'LE','Library Cache Lock', 'LF','Library Cache Lock', 'LG','Library Cache Lock', 'LH','Library Cache Lock', 'LI','Library Cache Lock', 'LJ','Library Cache Lock', 'LK','Library Cache Lock',

'LL','Library Cache Lock', 'LM','Library Cache Lock', 'LN','Library Cache Lock', 'LO','Library Cache Lock', 'LP','Library Cache Lock', 'MM','Mount Definition', 'MR','Media Recovery', 'NA','Library Cache Pin', 'NB','Library Cache Pin', 'NC','Library Cache Pin', 'ND','Library Cache Pin', 'NE','Library Cache Pin', 'NF','Library Cache Pin', 'NG','Library Cache Pin', 'NH','Library Cache Pin', 'NI','Library Cache Pin', 'NJ','Library Cache Pin', 'NK','Library Cache Pin', 'NL','Library Cache Pin', 'NM','Library Cache Pin', 'NN','Library Cache Pin', 'NO','Library Cache Pin', 'NP','Library Cache Pin', 'NQ','Library Cache Pin', 'NR','Library Cache Pin', 'NS','Library Cache Pin', 'NT','Library Cache Pin', 'NU','Library Cache Pin', 'NV','Library Cache Pin', 'NW','Library Cache Pin', 'NX','Library Cache Pin', 'NY','Library Cache Pin', 'NZ','Library Cache Pin', 'PF','Password File', 'PI','Parallel Slaves', 'PR','Process Startup', 'PS','Parallel Slave Synchronization', 'QA','Row Cache Lock', 'QB','Row Cache Lock', 'QC','Row Cache Lock', 'QD','Row Cache Lock', 'QE','Row Cache Lock', 'QF','Row Cache Lock', 'QG','Row Cache Lock', 'QH','Row Cache Lock', 'QI','Row Cache Lock', 'QJ','Row Cache Lock', 'QK','Row Cache Lock', 'QL','Row Cache Lock', 'QM','Row Cache Lock', 'QN','Row Cache Lock', 'QO','Row Cache Lock', 'QP','Row Cache Lock', 'QQ','Row Cache Lock', 'QR','Row Cache Lock', 'QS','Row Cache Lock', 'QT','Row Cache Lock', 'QU','Row Cache Lock',

'QV','Row Cache Lock', 'QW','Row Cache Lock', 'QX','Row Cache Lock', 'QY','Row Cache Lock', 'QZ','Row Cache Lock', 'RT','Redo Thread', 'SC','System Commit number', 'SM','SMON synchronization', 'SN','Sequence Number', 'SQ','Sequence Enqueue', 'SR','Synchronous Replication', 'SS','Sort Segment', 'ST','Space Management Transaction', 'SV','Sequence Number Value', 'TA','Transaction Recovery', 'TM','DML Enqueue', 'TS','Table Space (or Temporary Segment)', 'TT','Temporary Table', 'TX','Transaction', 'UL','User-defined Locks', 'UN','User Name', 'US','Undo segment Serialization', 'WL','Writing redo Log', 'XA','Instance Attribute Lock', 'XI','Instance Registration Lock') type_desc, */ --id1,id2, lmode,request,block,ctime ,p.spid, p.pid,s.process,q.sql_id, --s.seconds_in_wait, s.blocking_session_status, s.blocking_session, substr(sql_text,1,30) sql_text, do.object_name from v$lock l, v$session s, v$sqlarea q, v$locked_object lo, dba_objects do,v$process p where l.sid = s.sid and (l.id1, l.id2, l.type) IN (SELECT id1, id2, type FROM V$LOCK WHERE lmode > 0 and block > 0 and ctime > 300 ) and s.paddr = p.addr and s.sql_address = q.address(+) and s.sql_hash_value = q.hash_value(+) and l.sid = lo.session_id(+) and lo.object_id = do.object_id order by l.type,id1,lock_status desc,sid / I scheduled the lock script to run every ten minutes using this shell script and email me the results. $ cat mon_lock.sh #!/usr/bin/ksh cd /home/oracle/scripts export [email protected] export HOST_NAME=`hostname` cat /etc/oratab|grep -v "^#"|while read LINE do export ORACLE_SID=`echo $LINE|cut -d":" -f1`

export ORACLE_HOME=`echo $LINE|cut -d":" -f2` if [[ $(ps -ef|grep smon|grep $ORACLE_SID|grep -v grep|wc -l) > "0" ]]; then cp /dev/null mon_lock_$ORACLE_SID.log $ORACLE_HOME/bin/sqlplus -s "/as sysdba" >> mon_lock_$ORACLE_SID.log <<EOF set echo off feedback off head off @/home/oracle/scripts/mon_lock.sql EOF else echo "The instance is down" fi if [[ $(cat mon_locks_$ORACLE_SID.log|wc -l) > "0" ]]; then mailx -s "Locks waiting for > 5 mts in $ORACLE_SID" $MAIL_LIST < mon_lock_$ORACLE_SID.log fi done exit Reference: www.psoug.org