-- Create user DBMONITOR with DBA role (or at least the ability to perform the following script and execute it) DROP TABLE DBMONITOR.LOCKS_SQL; CREATE TABLE DBMONITOR.LOCKS_SQL ( SID NUMBER NOT NULL, USERNAME VARCHAR2(30) NULL, LOGON_TIME DATE NULL, MACHINE VARCHAR2(64) NULL, SQL_HASH_VALUE NUMBER NULL, PIECE NUMBER NULL, SQL_TEXT VARCHAR2(64) NULL, PREV_HASH_VALUE NUMBER NULL, CR_DATE DATE NULL ) TABLESPACE TBS_AAAAA NOLOGGING NOPARALLEL NOCACHE; commit; Grant select on DBMONITOR.LOCKS_SQL to public; commit; CREATE OR REPLACE PROCEDURE DBMONITOR.SP_LOCKS_CAPTURE IS /* Created by Roger Eisentrager and Shawn Aucoin Date November 01 2004 */ CURSOR locksqls IS SELECT vs.sid, vs.username, vs.logon_time, vs.machine, vs.sql_hash_value, vsql.piece, vsql.sql_text, vs.prev_hash_value FROM V$SESSION vs, v$sqltext vsql WHERE SID IN ( SELECT lpad(' ',DECODE(vl.request,0,0,1))||sid FROM V$LOCK vl WHERE id1 IN ( SELECT id1 FROM V$LOCK vl2 WHERE vl2.lmode = 0) ) AND vs.sql_hash_value = vsql.hash_value ORDER BY 2,5,6; jbs_rec locksqls%ROWTYPE; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'SP_LOCKS_CAPTURE', action_name => 'Find Locks for DBA'); FOR jbs_rec IN locksqls LOOP insert into DBMONITOR.LOCKS_SQL values ( jbs_rec.SID, jbs_rec.username, jbs_rec.logon_time, jbs_rec.machine, jbs_rec.sql_hash_value, jbs_rec.piece, jbs_rec.sql_text, jbs_rec.prev_hash_value, sysdate); commit; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line('Error : ' || SQLCODE || ' -- ' || SQLERRM); DBMS_APPLICATION_INFO.SET_MODULE(null,null); END; / CREATE OR REPLACE PROCEDURE DBMONITOR.SP_ALARM_MONITOR_LOCKS AS /* Created by Roger Eisentrager and Shawn Aucoin Date November 01 2004 */ CURSOR sql_f_locks IS SELECT vs.sid, vs.username, vs.logon_time, vs.machine, vs.sql_hash_value, vsql.piece, vsql.sql_text, vs.prev_hash_value FROM V$SESSION vs, v$sqltext vsql WHERE SID IN ( SELECT lpad(' ',DECODE(vl.request,0,0,1))||sid FROM V$LOCK vl WHERE id1 IN ( SELECT id1 FROM V$LOCK vl2 WHERE vl2.lmode = 0) ) AND vs.sql_hash_value = vsql.hash_value ORDER BY 2,5,6; jbs_rec sql_f_locks%ROWTYPE; email VARCHAR2(50) := ''; send_note boolean := FALSE; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'SP_ALARM_MONITOR_LOCKS', action_name => 'Find Locks for DBA'); FOR jbs_rec IN sql_f_locks LOOP email := CASE WHEN jbs_rec.sid > 1 THEN 'dba.guy@ourcompany.com' ELSE 'na' END; IF email <> 'na' THEN send_note := TRUE; END IF; EXIT WHEN send_note; END LOOP; IF send_note THEN SYS.mail_files ( 'PROD', email, 'PROD DB sql locks found', 'Check PROD db for blocking locks' ); DBMONITOR.SP_LOCKS_CAPTURE; END IF; DBMS_APPLICATION_INFO.SET_MODULE(null,null); END; / -- Run this in DB every 10 minutes (in place of " Thursday, November 1, 2004 1 00 00 PM " put in current date and time): DECLARE jobno number; BEGIN DBMS_JOB.SUBMIT(jobno, 'DBMONITOR.SP_ALARM_MONITOR_LOCKS;', TO_DATE('Thursday, November 1, 2004 1 00 00 PM', 'DAY, MONTH DD, YYYY HH:MI:SS AM'), 'SYSDATE+10/(24*60)'); COMMIT; END; / -- Then to find the users causing the Transaction Blocking Locks just do a the following SQL (even after blocking lock went away): select * from DBMONITOR.LOCKS_SQL; -- Here is the SQL to find Blocking locks in Oracle 9i r2 which is what we used in the procs above SELECT vs.sid, vs.username, vs.logon_time, vs.machine, vs.sql_hash_value, vsql.piece, vsql.sql_text, vs.prev_hash_value FROM V$SESSION vs, v$sqltext vsql WHERE SID IN ( SELECT lpad(' ',DECODE(vl.request,0,0,1))||sid FROM V$LOCK vl WHERE id1 IN ( SELECT id1 FROM V$LOCK vl2 WHERE vl2.lmode = 0) ) AND vs.sql_hash_value = vsql.hash_value ORDER BY 2,5,6; -- In case you do not use the Sys.MAIL package here it is ( can be found out in the internet as well): -- ====================================================== CREATE OR REPLACE PROCEDURE SYS.mail_files ( from_name varchar2, to_name varchar2, subject varchar2, message varchar2, max_size number default 9999999999, filename1 varchar2 default null, filename2 varchar2 default null, filename3 varchar2 default null, debug number default 0 ) is /* This procedure uses the UTL_SMTP package to send an email message. Up to three file names may be specified as attachments. Parameters are: 1) from_name (varchar2) 2) to_name (varchar2) 3) subject (varchar2) 4) message (varchar2) 5) max_size (number) 5) filename1 (varchar2) 6) filename2 (varchar2) 7) filename3 (varchar2) eg. mail_files( from_name => 'oracle' , to_name => 'someone@somewhere.com' , subject => 'A test', message => 'A test message', filename1 => '/data/oracle/dave_test1.txt', filename2 => '/data/oracle/dave_test2.txt'); */ v_smtp_server varchar2(10) := 'localhost'; v_smtp_server_port number := 25; v_directory_name varchar2(100); v_file_name varchar2(100); v_line varchar2(1000); crlf varchar2(2):= chr(13) || chr(10); mesg varchar2(32767); conn UTL_SMTP.CONNECTION; type varchar2_table is table of varchar2(4096) index by binary_integer; file_array varchar2_table; i binary_integer; v_file_handle utl_file.file_type; v_slash_pos number; mesg_len number; mesg_too_long exception; invalid_path exception; mesg_length_exceeded boolean := false; begin -- first load the three filenames into an array for easier handling later ... file_array(1) := filename1; file_array(2) := filename2; file_array(3) := filename3; -- Open the SMTP connection ... -- ------------------------ conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port ); -- Initial handshaking ... -- ------------------- utl_smtp.helo( conn, v_smtp_server ); utl_smtp.mail( conn, from_name ); utl_smtp.rcpt( conn, to_name ); utl_smtp.open_data ( conn ); -- build the start of the mail message ... -- ----------------------------------- mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf || 'From: ' || from_name || crlf || 'Subject: ' || subject || crlf || 'To: ' || to_name || crlf || 'Mime-Version: 1.0' || crlf || 'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || crlf || '' || crlf || 'This is a Mime message, which your current mail reader may not' || crlf || 'understand. Parts of the message will appear as text. If the remainder' || crlf || 'appears as random characters in the message body, instead of as' || crlf || 'attachments, then you''ll have to extract these parts and decode them' || crlf || 'manually.' || crlf || '' || crlf || '--DMW.Boundary.605592468' || crlf || 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || crlf || 'Content-Disposition: inline; filename="message.txt"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || '' || crlf || message || crlf ; mesg_len := length(mesg); if mesg_len > max_size then mesg_length_exceeded := true; end if; utl_smtp.write_data ( conn, mesg ); -- Append the files ... -- ---------------- for i in 1..3 loop -- Exit if message length already exceeded ... exit when mesg_length_exceeded; -- If the filename has been supplied ... if file_array(i) is not null then begin -- locate the final '/' or '\' in the pathname ... v_slash_pos := instr(file_array(i), '/', -1 ); if v_slash_pos = 0 then v_slash_pos := instr(file_array(i), '\', -1 ); end if; -- separate the filename from the directory name ... v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 ); v_file_name := substr(file_array(i), v_slash_pos + 1 ); -- open the file ... v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' ); -- generate the MIME boundary line ... mesg := crlf || '--DMW.Boundary.605592468' || crlf || 'Content-Type: application/octet-stream; name="' || v_file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || v_file_name || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf ; mesg_len := mesg_len + length(mesg); utl_smtp.write_data ( conn, mesg ); -- and append the file contents to the end of the message ... loop utl_file.get_line(v_file_handle, v_line); if mesg_len + length(v_line) > max_size then mesg := '*** truncated ***' || crlf; utl_smtp.write_data ( conn, mesg ); mesg_length_exceeded := true; raise mesg_too_long; end if; mesg := v_line || crlf; utl_smtp.write_data ( conn, mesg ); mesg_len := mesg_len + length(mesg); end loop; exception when utl_file.invalid_path then if debug > 0 then dbms_output.put_line('Error in opening attachment '|| file_array(i) ); end if; -- All other exceptions are ignored .... when others then null; end; mesg := crlf; utl_smtp.write_data ( conn, mesg ); -- close the file ... utl_file.fclose(v_file_handle); end if; end loop; -- append the final boundary line ... mesg := crlf || '--DMW.Boundary.605592468--' || crlf; utl_smtp.write_data ( conn, mesg ); -- and close the SMTP connection ... utl_smtp.close_data( conn ); utl_smtp.quit( conn ); end; / GRANT EXECUTE ON SYS.MAIL_FILES TO "PUBLIC"; commit;