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