Monitor Database Locks

monitor_locks.sh
#!/usr/bin/ksh
################################################################
# This script is used to find the database locks and alert the DBA.
# $1 is the instance name in which has database locks.
# Author : Anbu S Subramanian
#Date : 01/17/2006 #################################################################
. /oracle/.102boraenv
INST_DIR=$HOME/monitor_locks
opwd=`cat $INST_DIR/.infodb.$1`
export opwd
dba_team='anbus@1234'
dtstamp=`date +%m%d%Y%H%M%S`
sqlplus -s > /dev/null <
$opwd@$1
set feedback off
set echo off
set pages 100
set lines 100
spool $INST_DIR/find_$1_locks_$dtstamp.log
start $INST_DIR/find_locks.sql
spool off
END1
/usr/bin/cat $INST_DIR/find_$1_locks_$dtstamp.log grep WARNING > /dev/null 2>&1
if [ $? = 0 ]
then
mailx -s "WARNING!! THERE ARE LOCKS in $1 on $dtstamp holding for more than 3 minutes." $dba_team< $INST_DIR/find_$1_locks_$dtstamp.log
###else
###mailx -s "COOL NO LOCKS IN $1 ON $dtstamp" $dba_team< $INST_DIR/find_$1_locks_$dtstamp.log
fi
rm $INST_DIR/find_$1_locks_$dtstamp.log
exit

find_locks.sql
set serveroutput on size 1000000

set feedback off

set echo off

set pages 1000

set line 1000

Declare

Cursor find_cur is

Select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, do.owner, do.object_name, do.object_type, lo.locked_mode, dl.last_convert

From v$locked_object lo, dba_objects do, v$session sess, dba_locks dl

Where do.object_id = lo.object_id

and lo.session_id = sess.sid

and sess.sid= dl.session_id

and dl.last_convert >300

and dl.BLOCKING_OTHERS ='Blocking';
v_find_cur find_cur%rowtype;

v_count1 integer := 0;

v_sqlerrm varchar2(200) := null;

v_sqlcode number := 0;

v_instance varchar2(30);

v_date_time varchar2(30);

Begin

If find_cur%isopen

then

close find_cur;

end if;
Select upper(instance_name) into v_instance from v$instance;

select to_char(sysdate,'mm-dd-yyyy:hh:mi:ss') into v_date_time from dual;

select count(*) into v_count1 from v$locked_object lo, dba_objects do, v$session sess, dba_locks dl

Where do.object_id = lo.object_id

and lo.session_id = sess.sid

and sess.sid= dl.session_id

and dl.last_convert >300

and dl.BLOCKING_OTHERS ='Blocking';

If v_count1 > 0 then

dbms_output.put_line('WARNING!!!!!!!!!THERE ARE 'v_count1 ' LOCKS IN ' v_instance' holding more than 3 minutes');
open find_cur;
loop

fetch find_cur into v_find_cur;

exit when find_cur%notfound;

dbms_output.put_line('SID'': ' v_find_cur.sid ' Serial#'': ' v_find_cur.serial# ' Username'': 'v_find_cur.oracle_username ' os_user'': 'v_find_cur.os_user_name ' object_owner'': 'v_find_cur.owner' object_name'': 'v_find_cur.object_name ' object_type'': v_find_cur.object_type ' locked_mode'': 'v_find_cur.locked_mode ' lock_time in Seconds'': ' v_find_cur.last_convert);

Insert into xxxx.monitor_locks (INSTANCE, SID, SERIAL#, USERNAME, OSUSER,OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, LOCKED_MODE,LOCK_TIME_IN_SECONDS, DATE_TIME )

values (v_instance, v_find_cur.sid, v_find_cur.serial#, v_find_cur.oracle_username, v_find_cur.os_user_name, v_find_cur.owner, v_find_cur.object_name, v_find_cur.object_type, v_find_cur.locked_mode, v_find_cur.last_convert, v_date_time);

COMMIT;

END LOOP;

CLOSE find_cur;

Else

Null;

End if;

EXCEPTIONWHEN OTHERS THEN

V_SQLERRM := SUBSTR(SQLERRM,1,200);

V_SQLCODE := SQLCODE;DBMS_OUTPUT.PUT_LINE ('ERROR 'V_SQLERRM V_SQLCODE);

END;

/