Monitor all blocking sessions and get email alerts
                                                 Last update (2008-02-26 17:27:29)
                                                                                                                   Date added (2008-02-26 17:19:56)

Summary
You can create a "monitor procedure" that checks for blocking sessions and notifies with emails for blocks.

Explanation
You need to setup.
1. One procedure that sends emails and recipient
2. One procedure(Check_Blocking)
3. One jobs that checks every 30 minutes

2. One procedure

CREATE OR REPLACE PROCEDURE Check_Blocking
IS
  CURSOR CUR_RECIPIENTS IS SELECT * FROM HELPDESK.S_RECIPIENTS;
  V_DATABASE V$PARAMETER.VALUE%TYPE;
  crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
  count_blocking NUMBER;

BEGIN

SELECT VALUE INTO V_DATABASE FROM v$parameter WHERE NAME = 'instance_name';

SELECT COUNT(*)
INTO count_blocking
FROM V$SESSION WHERE
SID IN (SELECT SID FROM v$lock WHERE BLOCK = 1);
  IF count_blocking > 0 THEN
   FOR c1 IN cur_recipients LOOP
     BEGIN
     Send_Mail('skaparelis@ote.gr', c1.recipient, 'Subject: ' || 'DB Server:  ' || V_DATABASE || '  '|| 'Blocking sessions: ' || count_blocking || '' || crlf, crlf);
     END;
   END LOOP;   
  END IF;

END Check_Blocking;
/

3. One jobs that checks every 30 minutes

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SYS.CHECK_BLOCKING;'
     ,next_date  => TO_DATE('26/02/2008 17:00:31','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL   => 'SYSDATE+30/1440 '
     ,no_parse   => FALSE
    );
END;