Blocking session ... detect, unblock, notify

Blocking sessions are very unpleasant event on any database. With bigger number of users (sessions) possibility that one session blocks other is significantly higher. Every DBA is facing problems which could be briefly express in next three points:

1. How to detect that blocking has come?
2. How to unblock situation?
3. How to act proactively (be notified)?

If blocking session occure, DBA has two choice: to ask that user to cancel his work or to manually killed blocking session. In a cases when first rule cannot be applied (long running job or not reachable user) kill session will occurs. Generally speaking killing session can be perform with ALTER SYSTEM DISCONNECT  SESSION …  IMMEDIATE (preferable method) or ALTER SYSTEM KILL SESSION… IMMEDIATE statement. There are some situations when first statement would not be successful so second one can be used. DISCONNECT SESSION is more gentle and cleaner method because it removes killed session entries all around Oracle. KILL SESSION may leave some entries in Oracle instance so you may notice that some killed sessions still have MARKED FOR KILL label. Regardless this session is really gone mentioned label will remain until next restart of instance.

Detecting blocking sessions

One and most common way in recent time, is use Oracle Enterprise Manager or other visual tool (i.e. Quest’s Toad), where all information is served to you without knowing what is behind the scene or where these data are placed. Better (but harder) approach for any DBA is to use sql script that can retrieve data from Oracle database in a classic way and where you have to understand more deeply the problem and Oracle internal functionality. There are many postings which shows variety way of detecting blocking locks with sql code. Me as young DBA has one script which was functioning very well until 3 years ago where I face with Oracle RAC and problems of multi instance world. Then I have to rearrange the logic to support RAC as well non RAC database. This was the moment where following script has been reborn.

/* ---------------------------------------------------------------------------
Filename: sb.sql
CR/TR#  :
Purpose : Find blocking sessions and create release statements for RAC and non RAC environment

Date    : 08.07.2005.
Author  : Damir Vadas

Remarks : Tested on 10g only (should work on 11g as well) 
          run as privileged  user

Changes (DD.MM.YYYY, Name, CR/TR#):
          08.08.2006, Damir Vadas
                      Change the whole logic to accept RAC databases
          11.07.2009, Damir Vadas 
                      tuned a litle bit some queries
--------------------------------------------------------------------------- */

set serveroutput on
set linesize 266
set pagesize 600
set trimout on

declare
  cursor c_blockers IS
    SELECT DISTINCT
       username blocker_user,
       gvb.sid,
       gvs.serial# serial,
       gvb.inst_id,
       gvb.ctime ,
       module,
       decode(gvb.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-'
       ) lock_type,
       action
     FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvb.sid=gvs.sid
      AND gvb.inst_id=gvs.inst_id
  ORDER BY CTIME desc
  ;
 
  CURSOR c_w IS
    SELECT  gvw.sid waiter_sid,
            gvw.inst_id waiter_inst_id
    FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvb.sid=gvs.sid
      AND gvb.inst_id=gvs.inst_id
  ORDER BY gvb.CTIME desc
  ;
 
  CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT /* ordered */
      username waiter_user,
      gvw.sid,
      gvs.serial# serial,
      gvw.inst_id,
      gvw.ctime ,
      module,
      decode(gvw.request, 0, 'None',
                         1, 'NoLock',
                         2, 'Row-Share',
                         3, 'Row-Exclusive',
                         4, 'Share-Table',
                         5, 'Share-Row-Exclusive',
                         6, 'Exclusive',
                            'Nothing-'
      ) lock_req,
      action
    FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvw.sid=gvs.sid
      AND gvw.inst_id=gvs.inst_id
      AND gvw.sid=p_blocker_waiter_sid
      AND gvw.inst_id=p_blockers_waiter_inst_id
    ORDER BY CTIME desc
  ;

  i NUMBER := 0;
  bHasAny  boolean;
  bOnTheSameNode boolean;
  l_inst_id BINARY_INTEGER := 0;
  v_sid number := 0;
  v_serial NUMBER := 0;
  v_instance BINARY_INTEGER := 0;
  db_ver                 VARCHAR2(128);
  db_ver2                VARCHAR2(128); 
  s_job_command VARCHAR2 (256);
  s VARCHAR2(1024);
 
  PROCEDURE kill_by_hand is
  BEGIN
    DBMS_OUTPUT.PUT_LINE (chr(9)||'ALTER SYSTEM DISCONNECT  SESSION '''||v_sid||','||v_serial||''' IMMEDIATE;');
    DBMS_OUTPUT.PUT_LINE (chr(9)||'ALTER SYSTEM KILL        SESSION '''||v_sid||','||v_serial||''' IMMEDIATE;');   
  END; 
 
begin
  DBMS_OUTPUT.ENABLE(200000);
  dbms_utility.db_version(db_ver,db_ver2);
  DBMS_OUTPUT.PUT_LINE('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  DBMS_OUTPUT.PUT_LINE (chr(9));
  bHasAny := FALSE;
  bOnTheSameNode := FALSE;
  FOR v_blockers in c_blockers loop
    bHasAny := TRUE;    
    IF (i=0) THEN                 
      select instance_number into l_inst_id from v$instance;   
      bOnTheSameNode := (v_blockers.inst_id=l_inst_id);
      DBMS_OUTPUT.PUT_LINE (rpad('Blocker',12,' ')||'  '||'  Inst  '||rpad('SID',6,' ')||'  '||rpad('Serial',6,' ')||'  '||'  '||rpad('[sec]',6,' ')||' '||
                            lpad('Lock Type',10,' ')||' '|| lpad('Module',13,' ')
                           );
      dbms_output.put_line(rpad('-',120,'-'));
    END IF;
    v_sid := v_blockers.sid;
    v_serial := v_blockers.serial;
    v_instance := v_blockers.inst_id ;
    DBMS_OUTPUT.PUT_LINE(LPAD(to_char(i+1),3,' ')||'. '||rpad(v_blockers.blocker_user,12,' ')||'  '||rpad(v_blockers.inst_id,4,' ')||'  '||rpad(v_blockers.SID,8,' ' )||'  '||lpad(v_blockers.serial,5,' ')||'  '||lpad(v_blockers.CTIME,7,' ')||' '||
               lpad(v_blockers.lock_type,13,' ')||lpad(v_blockers.module,15,' ')
               );
    FOR v_w in c_w LOOP
      FOR v_waiters IN c_waiters (v_w.waiter_sid,v_w.waiter_inst_id) LOOP
        DBMS_OUTPUT.PUT_LINE (lpad(chr(9),1)||rpad(v_waiters.waiter_user,10,' ')||'  '||rpad(to_char(v_waiters.inst_id),4,' ')||rpad(to_char(v_waiters.sid),4,' ')|| ' '||lpad(to_char(v_waiters.serial),12,' ')||' ' ||' '||
                    ' ' ||lpad(to_char(v_waiters.ctime),6,' ')||' '|| lpad(to_char(v_waiters.lock_req),13,' ') || ' '|| lpad(to_char(v_waiters.module),15,' ')
                    );     
      END LOOP;
    END LOOP;
    IF bHasAny THEN
      i:=i+1;
    END IF;
  END LOOP;
  IF bHasAny THEN
    DBMS_OUTPUT.PUT_LINE (chr(9));
    DBMS_OUTPUT.PUT_LINE ('To kill first from the list, perform: ');
    DBMS_OUTPUT.PUT_LINE (chr(9));
    IF bOnTheSameNode THEN
      DBMS_OUTPUT.PUT_LINE (chr(9)||'exec dba_tools.safe_kill('||v_sid||','||v_serial||');');
      DBMS_OUTPUT.PUT_LINE (chr(9));
      kill_by_hand;
    ELSE
      IF db_ver like '10%' THEN
        s := '''begin execute immediate ''''ALTER SYSTEM KILL SESSION '''''''''||v_sid||','||v_serial||''''''''' IMMEDIATE''''; end; ''';
        dbms_output.put_line(s);
        DBMS_OUTPUT.PUT_LINE (chr(9));
        s_job_command := 'declare'|| chr(10)||
                         'v_job binary_integer;'|| chr(10)||
                         'begin'|| chr(10)||
                         'DBMS_JOB.submit (job=>v_job'||chr(10)||
                         ',what=>'||s||chr(10)||
                         ',instance=>'||v_instance||chr(10)||
                         '); '||chr(10)||
                         'DBMS_OUTPUT.PUT_LINE(to_char(v_job));'||chr(10)||
                         'commit;'||chr(10)||
                         'end;'|| chr(10) ||
                         '/'
        ;
        dbms_output.put_line(s_job_command);
      ELSIF db_ver like '11%' THEN
        DBMS_OUTPUT.PUT_LINE (chr(9)||'exec dba_tools.safe_kill('||v_sid||','||v_serial||','||v_instance||');');
        DBMS_OUTPUT.PUT_LINE (chr(9));
        DBMS_OUTPUT.PUT_LINE (chr(9)||'ALTER SYSTEM DISCONNECT  SESSION '''||v_sid||','||v_serial||',@'||v_instance||''' IMMEDIATE;');
        DBMS_OUTPUT.PUT_LINE (chr(9)||'ALTER SYSTEM KILL        SESSION '''||v_sid||','||v_serial||',@'||v_instance||''' IMMEDIATE;');   
      ELSE
        dbms_output.put_line('Try to logon as priveledged user on node ('||v_instance||') and perform:');
        kill_by_hand;
      END IF;
    END IF;
  ELSE
    dbms_output.put_line('No blocking users or process!');
  END IF;
end;
/

Remove blocking session

Output of this script not only detects blocking sessions but also shows how to remove them on RAC and non RAC environment. The other benefit of my approach is sb.sql script detects Oracle versions and for 10g on RAC create script which kill blocking session independently of the node where DBA connects. Here is an example of on RAC database (worst case scenario):

15:54:05 DAMIRV@test3> @sb
Oracle version: 10.1.0.4.0 (10.1.0.4.0)
      
Blocker         Inst  SID     Serial    [sec]   Lock Type        Module
-----------------------------------------------------------------------------------
1. SCOTT        1     153        7087       93   Transaction       SQL*Plus
        JOHN        3   133          2781        5     Exclusive        SQL*Plus 
        MARRY       2   154         58928       75     Exclusive        SQL*Plus
      
To kill first from the list, perform:
      
ALTER SYSTEM DISCONNECT  SESSION '153,7087' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '153,7087' IMMEDIATE;
      
declare
v_job binary_integer;
begin
DBMS_JOB.submit (job=>v_job
,what=>'begin execute immediate ''ALTER SYSTEM KILL SESSION ''''153,7087'''' IMMEDIATE''; end; '
,instance=>1
);
DBMS_OUTPUT.PUT_LINE(to_char(v_job));
commit;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.64
15:54:15 DAMIRV@test3>

As you can see user SCOTT (instance number 1)  is blocking two sessions, JOHN (connected on instance 3) and MARRY (on instance 2) for 5 and 75 seconds. DBA has connected with sqlplus tool on instance 3 (look at the sql prompt-DAMIRV@test3).
To use ordinary DISCONNECT SESSION command DBA must be on the same node as blocker, what sometimes is not easy to produce if RAC is driven with some load balancing system which connect to node with most free resources. For such a cases, beside classic command, script generate additional output where DBA kill session with job. To perform that solution copy/paste code that starts from “declare” . 
For the same situation Oracle 11g script will produce output like:

ALTER SYSTEM KILL        SESSION '153,7087,@1' IMMEDIATE;

what is enhancement in 11g because session can be killed without submitting a job but only declaring instance number, regardless on what node you are logged on.

Act proactively (be notified)!

Proactively here is not meant to avoid blocking sessions (this is subject which couldn’t be covered in several posts) but to find out when this occur before users start to complaint. To be fully informed I have used Oracle mail capability. In Oracle 10g UTL_MAIL is not installed by default. So from $ORACLE_HOME/rdbms/admin ($ORACLE_HOME\RDBMS\admin on Windows) directory, install as sysdba @utlmail.sql and @prvtmail.plb packages.
Examples here require a working SMTP mail server. Customize scripts to point the IP Address of your SMTP server. The SMTP port is usually 25. Configuring an SMTP server is not an Oracle function and thus out of scope for this site. In our case this is mail.iskon.hr which I use in Croatia. Then create check_bloking_session procedure, which is based on previous sb.sql code with adition and modification to comply mail feature.

CREATE OR REPLACE procedure TOOLS.check_blocking_session (
/* ---------------------------------------------------------------------------
 Filename: check_bloking_session.prc
 CR/TR#  :
 Purpose : Find blocking sessions and create release statements for RAC and non RAC environement 

 Date    : 18.07.2009.
 Author  : Damir Vadas

 Remarks : Tested on 10g only (should work on 11g as well and lower versions)
           To be able to send mail functionality "utl_mail" must be installed and granted to user
           Put in Oracle job with window of 5 minutes (default)

 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */

                                        p_sender          VARCHAR2 DEFAULT 'dba@noreply.com',
                                        p_recipients      VARCHAR2 DEFAULT 'damir.vadas@gmail.com',
                                        p_subject         VARCHAR2 DEFAULT 'Blocking session occured!',
                                        p_smtp_out_server VARCHAR2 DEFAULT 'mail.iskon.hr')

AS
  cursor c_blockers IS
    SELECT DISTINCT
       username blocker_user,
       gvb.sid,
       gvs.serial# serial,
       gvb.inst_id,
       gvb.ctime ,
       module,
       decode(gvb.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-'
       ) lock_type,
       action
     FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvb.sid=gvs.sid
      AND gvb.inst_id=gvs.inst_id
  ORDER BY CTIME desc
  ;
 
  CURSOR c_w IS
    SELECT  gvw.sid waiter_sid,
            gvw.inst_id waiter_inst_id
    FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvb.sid=gvs.sid
      AND gvb.inst_id=gvs.inst_id
  ORDER BY gvb.CTIME desc
  ;
 
  CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT /* ordered */
      username waiter_user,
      gvw.sid,
      gvs.serial# serial,
      gvw.inst_id,
      gvw.ctime ,
      module,
      decode(gvw.request, 0, 'None',
                         1, 'NoLock',
                         2, 'Row-Share',
                         3, 'Row-Exclusive',
                         4, 'Share-Table',
                         5, 'Share-Row-Exclusive',
                         6, 'Exclusive',
                            'Nothing-'
      ) lock_req,
      action
    FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvw.sid=gvs.sid
      AND gvw.inst_id=gvs.inst_id
      AND gvw.sid=p_blocker_waiter_sid
      AND gvw.inst_id=p_blockers_waiter_inst_id
    ORDER BY CTIME desc
  ;

  bHasAny  boolean;
  i        NUMBER := 0;
  v_sid    NUMBER := 0;
  v_serial NUMBER := 0;
  v_instance BINARY_INTEGER := 0;
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128); 
  s_job_command     VARCHAR2 (4000);
  s_mail_text       VARCHAR2 (4000);
  s_subject         VARCHAR2 (256);
  s_smtp_out_server VARCHAR2 (256);
  s_host_name       v$instance.host_name%TYPE;
  s_db_name         v$database.name%TYPE;
  s_instance_name   v$instance.instance_name%TYPE;
  s                 VARCHAR2(1024);
 
  PROCEDURE add_message_line (p_text VARCHAR2) is
  BEGIN
    s_mail_text := s_mail_text || p_text || chr(10);    
  END;
begin
  dbms_utility.db_version(db_ver,db_ver2);
  add_message_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  bHasAny := FALSE; 
  FOR v_blockers in c_blockers loop
    bHasAny := TRUE;    
    IF (i=0) THEN                 
      add_message_line (rpad('Blocker',12,' ')||'  '||'  Inst  '||rpad('SID',6,' ')||'  '||rpad('Serial',6,' ')||'  '||'  '||rpad('[sec]',6,' ')||' '||
                        lpad('Lock Type',10,' ')||' '|| lpad('Module',13,' ')
                        );
      add_message_line (rpad('-',120,'-'));
    END IF;
    v_sid := v_blockers.sid;
    v_serial := v_blockers.serial;
    v_instance := v_blockers.inst_id ;
    add_message_line (LPAD(to_char(i+1),3,' ')||'. '||rpad(v_blockers.blocker_user,12,' ')||'  '||rpad(v_blockers.inst_id,4,' ')||'  '||rpad(v_blockers.SID,8,' ' )||'  '||lpad(v_blockers.serial,5,' ')||'  '||lpad(v_blockers.CTIME,7,' ')||' '||
                      lpad(v_blockers.lock_type,13,' ')||lpad(v_blockers.module,15,' ')
                     );
    FOR v_w in c_w LOOP
      FOR v_waiters IN c_waiters (v_w.waiter_sid,v_w.waiter_inst_id) LOOP
        add_message_line (lpad(' ',5)||rpad(v_waiters.waiter_user,10,' ')||'  '||rpad(to_char(v_waiters.inst_id),4,' ')||rpad(to_char(v_waiters.sid),4,' ')|| ' '||lpad(to_char(v_waiters.serial),12,' ')||' ' ||' '||
                    ' ' ||lpad(to_char(v_waiters.ctime),6,' ')||' '|| lpad(to_char(v_waiters.lock_req),13,' ') || ' '|| lpad(to_char(v_waiters.module),15,' ')
                    );     
      END LOOP;
    END LOOP;
    IF bHasAny THEN
      i:=i+1;
    END IF;
  END LOOP;
  IF bHasAny THEN
    SELECT instance_name, host_name INTO s_instance_name, s_host_name FROM v$instance;
    SELECT name INTO s_db_name FROM v$database;
    s_subject := p_subject || '  '|| s_db_name || '('|| s_instance_name || ') on ' || s_host_name ||'';
    add_message_line ('');
    add_message_line ('To kill first from the list, perform: ');
    add_message_line ('');
    IF db_ver like '10%' THEN
      add_message_line ('NON RAC (or RAC logged on that node):');
      add_message_line ('---------------------------------');
      add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_sid||','||v_serial||''' IMMEDIATE;');
      add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_sid||','||v_serial||''' IMMEDIATE;');
      add_message_line ('');
      add_message_line ('');
      s := '''begin execute immediate ''''ALTER SYSTEM KILL SESSION '''''''''||v_sid||','||v_serial||''''''''' IMMEDIATE''''; end; ''';    
      add_message_line ('RAC (logged on any node) :');
      add_message_line ('--------------------------');
      s_job_command := 'declare'|| chr(10)||
                       '  v_job binary_integer;'|| chr(10)||
                       'begin'|| chr(10)||
                       '  DBMS_JOB.submit (job     =>v_job'||chr(10)||
                       '                  ,what    =>'||s||chr(10)||
                       '                  ,instance=>'||v_instance||chr(10)||
                       '  ); '||chr(10)||
                       '  commit;'||chr(10)||
                       'end;'|| chr(10) ||
                       '/'
      ;
      add_message_line (s_job_command);
    ELSIF db_ver like '11%' THEN
      add_message_line ('');
      add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_sid||','||v_serial||',@'||v_instance||''' IMMEDIATE;');
      add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_sid||','||v_serial||',@'||v_instance||''' IMMEDIATE;');   
    ELSE
      add_message_line ('');
      add_message_line ('Untested version... Try to logon as priveledged user on node ('||s_host_name||') and perform:');
      add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_sid||','||v_serial||''' IMMEDIATE;');
      add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_sid||','||v_serial||''' IMMEDIATE;');   
    END IF;
    IF p_smtp_out_server != 'mail.iskon.hr' THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '''||p_smtp_out_server||'''';
    END IF;
    add_message_line ('');
    add_message_line ('This is automated message so, do not reply to this mail.');
    add_message_line ('');
    select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into s from dual;
    UTL_MAIL.send(sender => p_sender,
              recipients => p_recipients,
                 subject => s_subject,
                 message => s_mail_text,
               mime_type => 'text/plain; charset=us-ascii');    
  END IF;
end check_blocking_session;
/

Then you have to create Oracle job, which will repeat checking periodically (in our case 5 minutes):

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT (
    job => X
   ,what => 'check_blocking_session;'
   ,next_date => to_date('13/11/2009 13:09:02','dd/mm/yyyy hh24:mi:ss')
   ,interval => 'SYSDATE+5/1440'
   ,no_parse => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
   COMMIT;
END;
/

Customization

With procedure parameters you might use this functionality in different scenarios. In our case, mail will be sent whenever single blocking occurs. Here are some covered situations:

• You may have some actions with partially acceptable blocking sessions (for some time or by time when they occur, for example especially at night) so you do not want to be informed about that.
• You may check how long blocking is happening-is it longer then some period you define as threshold.
• Is blocking caused by some higher priority user, what is perfectly acceptable to your situation.

For all these situations you may reconsider to change when mail should be sent. However, please understand that this is not my advice. I'll rather recheck code that is causing blocks or reschedule problematic events.