Monday, October 23, 2017

Procedure to receive emails of sessions blocked for more than 30 minutes


CREATE OR REPLACE procedure SYS.send_blocking_mail 
as
  V_CULPRIT_SID     NUMBER(10);
  V_CLUPRIT_SERIAL#  NUMBER(10);
  V_FROM      VARCHAR2(80) := 'farhat@oracle.com';
  V_RECIPIENT VARCHAR2(80) := 'farhatzaman@gmail.com';
  V_SUBJECT   VARCHAR2(80) := 'Blocking Sessions Alert';
  V_MAIL_HOST VARCHAR2(30) := 'mail.oracle.com';
  V_MAIL_CONN UTL_SMTP.CONNECTION;
  MSG CLOB ;
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
  BEGIN
select distinct
   b.sid , 
   b.sess_serial# 
INTO
V_CULPRIT_SID,V_CLUPRIT_SERIAL#
from 
   gv$session a, gv$session_blockers b
where 
a. sid=b.sid
and A.SERIAL# = B.SESS_SERIAL#
and
   a.blocking_session is not NULL
           and a.wait_class='Application'
           and a.seconds_in_wait >= 1800;
 MSG := MSG||' Dear';
 V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, V_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
   'DATE: '   || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||
   'FROM: '   || V_FROM || CRLF ||
   'SUBJECT: '|| V_SUBJECT || CRLF ||
   'TO: '     || V_RECIPIENT || CRLF ||
   CRLF || ' Attention ' || 'Session with SID ' || V_CULPRIT_SID || ' and Serial# ' || V_CLUPRIT_SERIAL# || CRLF ||
   'causing locks on database. Please kill this  session to release locks. ' 
);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION            
when no_data_found then
NULL;      
  END;
/

No comments:

Post a Comment