Blocking session ... detect, unblock, notify (Part II)

In my first post on blocking session problem (Blocking session ... detect, unblock, notify) I have shown how to handle these problems pretty clear and, from mine point of view, easy to understand. Recent events on one RAC database and little less experienced DBA, force me to make some updates on this subject to avoid all possible traps that mine solution may have in explanation.

The problem

In the middle of one day on one RAC database (3 nodes), "sb" procedure had output like:

SQL> @sb
Oracle version: 10.1.0.4.0 (10.1.0.4.0)
Blocker         Inst  SID     Serial    [sec]   Lock Type     Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction
       ZLSA        2   372           126     1128     Exclusive 
        SLPE        2   368            48     6836     Exclusive 
        DRI2        3   237           232     6727     Exclusive 
2. DRI2          3     237         232     6724   Transaction 
        DRIA        2   355           195      216     Exclusive  
        NESE        3   114            80     5527     Exclusive  
3. NESE          3     114          80     5521   Transaction
        DRI2        2   379           328      388   Share-Table 
        DRI2        3   115           269     5439   Share-Table 
        DRI2        3   423           619     3529     Exclusive 
        DRI2        2   373           108     5952   Share-Table 
        NESE        1   303          2811     4870     Exclusive 
        DRLO        3   205           749     2862     Exclusive 
        NESE        3   146           220     4766     Exclusive 
        
To kill first from the list, perform:
        
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '282,6661' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '282,6661' IMMEDIATE;
        
        
RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''282,6661'''' IMMEDIATE''; end; '
,instance=>1
); 
  commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

Previously mentioned DBA, was using mine solution for a long time without any problems and like in many situation before, submit a job like it was advised.

After some time when he checked blockers with "sb" procedure again, he was astonished that procedure produce the same output like before-like nothing happened. Without thinking why, he submit job again ... and later once again and after third submit that results no "unblocking", he came to me and ask for help.

The analyze part

Like in many situation like this, checking instance alert log file (this is RAC database) is the first step that DBA should do. It's content has at the end entries like:

ORA-12012: error on auto execute of job 1241
ORA-00030: User session ID does not exist.
ORA-06512: at line 1

Checking of dba_jobs shows me that 2 jobs are trying to execute again and again to perform "kill session" for the session that doesn't exist!

Logical question was "How session doesn't exist if "sb" is still showing it? "

After checking, answer was very easy-because of bug in it's logic!

If you look in code of "sb" procedure, you'll find this part in definition of blocking session:

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 ;

All looks OK (and in fact it is!!) but in some real cases, like this one was, it was not properly used. Later in code I loop through that cursor without checking status column (that I do not have at all!) of that session and shows all sessions that are blocked by every cursor entry. So shortly, it was wrong because this cursors shows all sessions regardless their status (which can be "ACTIVE", "INACTIVE", "KILLED" etc.) and some of them might get STATUS = 'KILLED'!
Beside this there was a problem with waiters that are mixed because there was no blockers SID passed as an parameter. So this is why I completely removed c_w cursor and put all in one c_waiters which has two parameters:

CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT 
      nvl(username,'Blocker ???') waiter_user, 
      gvl.sid, 
      gvs.serial# serial, 
      gvl.inst_id, 
      gvl.ctime , 
      gvs.status STATUS,
      module,
      decode(gvl.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 gvl, 
          gv$session gvs
    WHERE gvl.request>0
      AND gvl.sid=gvs.sid
      AND gvl.inst_id=gvs.inst_id
      AND gvs.blocking_session=p_blocker_waiter_sid
      AND gvl.inst_id=p_blockers_waiter_inst_id
  ORDER BY gvl.CTIME desc
  ;

And this is exactly what happened in this case. First submitted job really killed that blocking session ("mark for kill") and other two hasn't find anything to kill so they were trying to execute and after some time gone to broken status after n retries.

To make more clear, let me show you the same situation with added STATUS field:

Blocker         Inst  SID     Serial    [sec]   Lock Type     Status       Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction    KILLED
        ZLSA        2   372           126     1128     Exclusive     ACTIVE
        SLPE        2   368            48     6836     Exclusive     ACTIVE
        DRI2        3   237           232     6727     Exclusive     ACTIVE
2. DRI2          3     237         232     6724   Transaction    ACTIVE
        DRIA        2   355           195      216     Exclusive     ACTIVE
        NESE        3   114            80     5527     Exclusive     ACTIVE
3. NESE          3     114          80     5521   Transaction    ACTIVE
        DRI2        2   379           328      388   Share-Table     ACTIVE
        DRI2        3   115           269     5439   Share-Table     ACTIVE
        DRI2        3   423           619     3529     Exclusive     ACTIVE
        DRI2        2   373           108     5952   Share-Table     ACTIVE
        NESE        1   303          2811     4870     Exclusive     ACTIVE
        DRLO        3   205           749     2862     Exclusive     ACTIVE
        NESE        3   146           220     4766     Exclusive     ACTIVE

Now this all looks reasonable...but...then come the second bug. The second bug in sb procedure was that "kill statement" was always constructed from first blocker in list. And this was again wrong because this session may be killed and then this kill command would produce an error!

The solution

When I realize that, I rewrite a new sb procedure:

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

 Date    : 08.07.2005.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g only (should work on 11g as well and lower versions)

 Changes (DD.MM.YYYY, Name, CR/TR#):
          11.12.2009, Damir Vadas
                      added p_to and p_cc as parameters to avoid bad header encodding on hac server
                      (no success!) 
          30.12.2009, Damir Vadas
                      added checking (LENGTH(s_mail_text)+LENGTH(p_text)+1 <= 4000)
                      because "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
          06.01.2010, Damir Vadas
                      added 32k size of message and exception log handler
          30.04.2010, Damir Vadas
                      fixed bug with showing last blocker not first one in kill statement
                      added STATUS column to show if session is killed (needed for previous fixed)
          01.09.2010, Damir Vadas
                      fixed bug blockers and waiters to be 11g compatible
--------------------------------------------------------------------------- */ 
set serveroutput on size 123456
set linesize 140
set pagesize 100
set trimout on

declare
  cursor c_blockers IS
    SELECT DISTINCT 
       nvl(username,'BLOCKER ???') blocker_user, 
       gvl.sid, 
       gvs.serial# serial, 
       gvl.inst_id, 
       gvl.ctime ,
       gvs.status STATUS,      
       module, 
       action,
       decode(gvl.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
     FROM gv$lock gvl, 
          gv$session gvs
    WHERE gvl.request=0
      AND gvl.block=1
      AND gvl.sid=gvs.sid
      AND gvl.inst_id=gvs.inst_id
  ORDER BY 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 
      nvl(username,'WAITER ???') waiter_user, 
      gvl.sid, 
      gvs.serial# serial, 
      gvl.inst_id, 
      gvl.ctime , 
      gvs.status STATUS,
      module,
      decode(gvl.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 gvl, 
          gv$session gvs
    WHERE gvl.request>0
      AND gvl.sid=gvs.sid
      AND gvl.inst_id=gvs.inst_id
      AND gvs.blocking_session=p_blocker_waiter_sid
      AND gvl.inst_id=p_blockers_waiter_inst_id
  ORDER BY gvl.CTIME desc
  ;

  bHasAny  boolean;
  i        NUMBER := 0;
  v_sid    NUMBER := 0;
  v_serial NUMBER := 0;
  v_first_sid    NUMBER := -1;
  v_first_serial NUMBER := -1;
  v_instance        BINARY_INTEGER := 0;
  v_first_instance  BINARY_INTEGER := 0;
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
  s_job_command     VARCHAR2 (4000);
  s_mail_text       VARCHAR2 (32000);
  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
    -- for proceduire version with mail part
    -- s_mail_text := s_mail_text || p_text || chr(10);     
    dbms_output.put_line (p_text);
  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',8,' ')||'  '||'  '||rpad('[sec]',6,' ')||' '|| 
                        lpad('Lock Type',10,' ')||' '|| lpad('Status',14, ' ') || 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 ;
    -- only first blocker (who is not killed!) is important!
    IF v_blockers.status != 'KILLED' AND v_first_sid = -1 THEN
      v_first_sid := v_blockers.sid;
      v_first_serial := v_blockers.serial;
      v_first_instance := v_blockers.inst_id ;
    END IF;
    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.status,14,' ')||lpad(nvl(v_blockers.module,'?'),15,' ')
                     );
    FOR v_waiters IN c_waiters (v_blockers.sid,v_blockers.inst_id ) LOOP 
      add_message_line (lpad(chr(9),9)||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(v_blockers.status,14,' ')|| ' '|| lpad(to_char(nvl(v_waiters.module,'?')),15,' ')
                  );      
    END LOOP;
    IF bHasAny THEN
      i:=i+1;
    END IF;
  END LOOP;
  -- show any kind of blocker (killed one also...as info)
  IF bHasAny THEN
    -- show blocking rows info
    -- add_message_line (chr(9));
    -- show_blocked_records (s_blocked_rows);
    -- dbms_output.put_line(s_blocked_rows);
    -- add_message_line (s_blocked_rows);  
    -- for blockers that are valid (not killed!) create kill statement
    IF ( v_first_sid > 0 ) 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; 
      add_message_line (chr(9));
      add_message_line ('To kill first from the list, perform: ');
      add_message_line (chr(9));
      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_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line (chr(9));
        add_message_line (chr(9));
        s := '''begin execute immediate ''''ALTER SYSTEM KILL SESSION '''''''''||v_first_sid||','||v_first_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_first_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 (chr(9));
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');    
      ELSE
        add_message_line (chr(9));
        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_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');    
      END IF; 
    ELSE
      add_message_line ('All valid blocker sessions allready killed. Try kill with OS level command!');
    END IF;
  ELSE
    add_message_line ('No blocking sessions found!');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- auto_log_error_core('"check_blocking_session" procedure. ');
    null;
end;
/

Now when you run the sb procedure, the same situation will look like:

SQL> @sb
Oracle version: 10.1.0.4.0 (10.1.0.4.0)
Blocker         Inst  SID     Serial    [sec]   Lock Type     Status       Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction    KILLED
       ZLSA        2   372           126     1128     Exclusive     ACTIVE
        SLPE        2   368            48     6836     Exclusive     ACTIVE
        DRI2        3   237           232     6727     Exclusive     ACTIVE
2. DRI2          3     237         232     6724   Transaction    ACTIVE
        DRIA        2   355           195      216     Exclusive     ACTIVE
        NESE        3   114            80     5527     Exclusive     ACTIVE
3. NESE          3     114          80     5521   Transaction    ACTIVE
        DRI2        2   379           328      388   Share-Table     ACTIVE
        DRI2        3   115           269     5439   Share-Table     ACTIVE
        DRI2        3   423           619     3529     Exclusive     ACTIVE
        DRI2        2   373           108     5952   Share-Table     ACTIVE
        NESE        1   303          2811     4870     Exclusive     ACTIVE
        DRLO        3   205           749     2862     Exclusive     ACTIVE
        NESE        3   146           220     4766     Exclusive     ACTIVE
        
To kill first from the list, perform:
        
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '237,232' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '237,232' IMMEDIATE;
        
        
RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''237,232'''' IMMEDIATE''; end; '
,instance=>3
); 
  commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

So it would construct kill statement for second blocker and skip first blocker which is "marked for kill". And now this is regular.

Kill "marked for kill"

If you look in oracle theory, "KILLED" status does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. If the marked session persists for some time you may consider killing the process at the operating system level.

So practically it was still running in Oracle and the only way to immediately kill this session was to perform from OS level command!

Just to remind you differences between DISCONNECT and KILL SESSION

• IMMEDIATE clause with KILL SESSION
  instruct Oracle to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

• IMMEDIATE clause with DISCONNECT SESSION
  disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

If you are on Linux this action is peace of cake but on Windows ... good luck with "orakill" command, command which in most of the cases doesn't kill at all!

Be proactive

According this do not forget to replace other part of solution (proactive checking of blocking session-"check_blocking_session.prc") which I place in job with 5 minutes schedule and run automatically. Here is final version for that cause.

CREATE OR REPLACE procedure check_blocking_session (
/* $Header: check_blocking_session.prc 1.12 10/19/2010 20:45 damirv $ */
/* ---------------------------------------------------------------------------

           Copyright(C) 1995-2009 TEB Informatika d.o.o.


 Filename: check_blocking_session.prc
 CR/TR#  : 
 Purpose : Find blocking sessions and create release statements for RAC and non RAC environment  

 Date    : 08.07.2005.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g only (should work on 11g as well and lower versions)

 Changes (DD.MM.YYYY, Name, CR/TR#):
          11.12.2009, Damir Vadas
                      added p_to and p_cc as parameters to avoid bad header encodding on hac server
                      (no success!) 
          30.12.2009, Damir Vadas
                      added checking (LENGTH(s_mail_text)+LENGTH(p_text)+1 <= 4000)
                      because "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
          06.01.2010, Damir Vadas
                      added 32k size of message and exception log handler
          30.04.2010, Damir Vadas
                      fixed bug with showing last blocker not first one in kill statement
                      added STATUS column to show if session is killed (needed for previous fixed)
          01.09.2010, Damir Vadas
                      fixed bug blockers and waiters to be 11g compatible
          20.09.2010, Damir Vadas
                      Reunited logic for script/procedure
          19.10.2010, Damir Vadas
                      Removed p_smtp_out_server VARCHAR2 DEFAULT 'mail.iskon.hr' declaration
--------------------------------------------------------------------------- */
                                        p_sender          VARCHAR2 DEFAULT 'dba@teb-informatika.hr',
                                        p_to              VARCHAR2 DEFAULT 'damir.vadas@teb-informatika.hr',
                                        p_cc              VARCHAR2 DEFAULT null,
                                        p_subject         VARCHAR2 DEFAULT 'Blocking session occur!',
                                        p_send_mail       BOOLEAN  DEFAULT TRUE)
AS
  b_is_script BOOLEAN DEFAULT FALSE;
  CURSOR c_blockers IS
    SELECT DISTINCT 
           NVL(username,'BLOCKER ???') blocker_user, 
           gvb.sid, 
           gvs.serial# serial, 
           gvb.inst_id, 
           gvb.ctime ,
           gvs.status STATUS,      
           module, 
           action,
           NVL (
             decode(gvb.type, 
                       'AE', 'Edition Lock',                                          -- Prevent Dropping an edition in use 11g
                       'BL','Buffer hash table',
                       'CF','Control File Transaction',
                       'CI','Cross Instance Call',
                       'CS','Control File Schema',
                       'CU','Bind Enqueue',
                       'DF','Data File',
                       'DL','Direct-loader index-creation',
                       'DM','Mount/startup db primary/secondary instance',
                       'DR','Distributed Recovery Process',
                       'DX','Distributed Transaction Entry',
                       'FI','SGA Open-File Information',
                       'FS','File Set',
                       'IN','Instance Number',
                       'IR','Instance Recovery Serialization',
                       'IS','Instance State',
                       'IV','Library Cache InValidation',
                       'JQ','Job Queue',
                       'KK','Redo Log "Kick"',
                       'LS','Log Start/Log Switch',
                       'MB','Master Buffer hash table',
                       'MM','Mount Definition',
                       'MR','Media Recovery',
                       'PF','Password File',
                       'PI','Parallel Slaves',
                       'PR','Process Startup',
                       'PS','Parallel Slaves Synchronization',
                       'RE','USE_ROW_ENQUEUE Enforcement',
                       'RT','Redo Thread',
                       'RW','Row Wait',
                       'SC','System Commit Number',
                       'SH','System Commit Number HWM',
                       'SM','SMON',
                       'SQ','Sequence Number',
                       'SR','Synchronized Replication',
                       'SS','Sort Segment',
                       'ST','Space Transaction',
                       'SV','Sequence Number Value',
                       'TA','Transaction Recovery',
                       'TD','DDL enqueue',
                       'TE','Extend-segment enqueue',
                       'TM','DML enqueue',
                       'TO', 'Temp Object',                                              --Sync DDL and DML operations on a temp object
                       'TS','Temporary Segment',
                       'TT','Temporary Table',
                       'TX','Transaction',
                       'UL','User-defined Lock',
                       'UN','User Name',
                       'US','Undo Segment Serialization',
                       'WL','Being-written redo log instance',
                       'WS','Write-atomic-log-switch global enqueue',
                       'XA','Instance Attribute',
                       'XI','Instance Registration', 
                       decode(substr(gvb.type,1,1),
                                   'L','Library Cache     ('||substr(gvb.type,2,1)||')', -- Library cache lock instance lock (A..P = namespace)
                                   'N','Library Cache Pin ('||substr(gvb.type,2,1)||')', -- Library cache pin instance (A..Z = namespace)
                                   'Q','Row Cache         ('||substr(gvb.type,2,1)||')', -- Row cache instance (A..Z = cache)
                                   '????'
                       ),
                       'Bl. lt:"'||gvb.type||'"' 
                   ), 'NULL Block Type'
           ) lock_type
     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
  ;
  
  -- waiters 
  CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT 
           NVL(username,'WAITER ???') waiter_user, 
           gvs.sid, 
           gvs.serial# serial, 
           gvs.inst_id, 
           gvw.ctime , 
           gvs.status STATUS,
           module,
           action,
           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
     FROM gv$lock gvw, gv$session gvs
    WHERE 1=1
      AND gvw.request>0
      AND gvw.lmode=0
      AND gvw.sid=gvs.sid
      AND gvw.inst_id=gvs.inst_id
      AND gvs.blocking_session=p_blocker_waiter_sid 
      AND gvs.blocking_instance=p_blockers_waiter_inst_id
  ORDER BY CTIME desc
  ;
  
  bHasAny  boolean;
  i        NUMBER := 0;
  v_sid    NUMBER := 0;
  v_serial NUMBER := 0;
  v_first_sid    NUMBER := -1;
  v_first_serial NUMBER := -1;
  v_instance        BINARY_INTEGER := 0;
  v_first_instance  BINARY_INTEGER := 0;
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
  s_job_command     VARCHAR2 (4000);
  s_mail_text       VARCHAR2 (32000);
  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);
  
  s_blocked_rows    VARCHAR2(4000);
  
  l_temp            VARCHAR2(64);

  FUNCTION add_indent_char RETURN VARCHAR2 
  IS
  BEGIN
    IF b_is_script THEN
      RETURN chr(255);
    ELSE
      RETURN chr(9);
    END IF;
  END;

  PROCEDURE add_message_line (p_text VARCHAR2) is
  BEGIN
    dbms_output.put_line (p_text);
    s_mail_text := s_mail_text || p_text || chr(10);     
  END;
  
BEGIN
  b_is_script := NOT p_send_mail;
  add_message_line (chr(9));
  dbms_utility.db_version(db_ver,db_ver2);
  add_message_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  dbms_output.put_line (chr(9)); 
  bHasAny := FALSE;  
  FOR r_blockers in c_blockers loop
    bHasAny := TRUE;     
    IF (i=0) THEN       
      add_message_line (chr(9));    
      add_message_line (rpad('Blocker',17,' ')||
                        lpad('Inst',4,' ')||
                        lpad('SID',7,' ')||
                        lpad('Serial',8,' ')||
                        lpad('[sec]',10,' ')|| 
                        lpad('Lock Type/Req. lock',30,' ')||
                        lpad('Status',13, ' ') ||
                        lpad('Module',25,' ')
                        );
      add_message_line (rpad('-',120,'-'));
    END IF;
    v_sid := r_blockers.sid;
    v_serial := r_blockers.serial;
    v_instance := r_blockers.inst_id ;
    -- only first blocker (who is not killed!) is important!
    IF r_blockers.status != 'KILLED' AND v_first_sid = -1 THEN
      v_first_sid := r_blockers.sid;
      v_first_serial := r_blockers.serial;
      v_first_instance := r_blockers.inst_id ;
    END IF;
    -- print blockers sessions
    add_message_line (rpad((to_char(i+1)||'.'||r_blockers.blocker_user),17,' ')||
                      lpad(r_blockers.inst_id,4,' ')||
                      lpad(r_blockers.SID,7,' ' )||
                      lpad(r_blockers.serial,8,' ')||
                      lpad(r_blockers.CTIME,10,' ')||
                      lpad(r_blockers.lock_type,30,' ')||
                      lpad(r_blockers.status,13,' ')||
                      lpad(nvl(r_blockers.module,'?'),25,' ')
                     );
    -- print waiters session for current blocker
    FOR r_waiters IN c_waiters (r_blockers.sid ,r_blockers.inst_id) LOOP 
      IF b_is_script THEN
        l_temp := add_indent_char; -- || add_indent_char;
      ELSE
        l_temp := add_indent_char;
      END IF;
      add_message_line (l_temp||
                        rpad(r_waiters.waiter_user,17,' ')||
                        lpad(r_waiters.inst_id,4,' ')||
                        lpad(r_waiters.sid,7,' ')||
                        lpad(r_waiters.serial,8,' ')||
                        lpad(r_waiters.ctime,10,' ')||
                        lpad(r_waiters.lock_req,30,' ')|| 
                        lpad(r_waiters.status,13,' ')|| 
                        lpad(nvl(r_waiters.module,'?'),25,' ')
                  );      
    END LOOP;
    IF bHasAny THEN
      i:=i+1;
    END IF;
  END LOOP;
  
  IF (v_first_serial = 1 ) THEN
    add_message_line (chr(9));
    add_message_line ('Warning!');
    add_message_line ('-------------------------------------------------------------------------------');
    add_message_line ('Blocking sessions with serial=1 are core ORACLE PROCESSES.');
    add_message_line ('Killing them without REAL NEED and DOUBLE CHECKING may dangerous your instance!');
    add_message_line ('-------------------------------------------------------------------------------');
    add_message_line (chr(9));
  END IF;
  -- show any kind of blocker (killed one also...as info)
  IF bHasAny THEN
    IF NOT b_is_script THEN
      -- show blocking rows info
      add_message_line (chr(9));
      add_message_line ('---------------  Blocking records information  ---------------');
      add_message_line (chr(9));
      show_blocked_records (s_blocked_rows);
      IF s_blocked_rows is null THEN
        add_message_line(chr(9)||chr(9)||'No locked records found.');
      ELSE
        s_mail_text := s_mail_text || s_blocked_rows || chr(10);   
      END IF;
    END IF;
    -- for blockers that are valid (not killed!) create kill statement
    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 (chr(9));
    IF (v_first_sid > 0 ) THEN
      add_message_line ('To kill first from the list, perform: ');
      add_message_line (chr(9));
      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_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line (chr(9));
        add_message_line (chr(9));
        s := '''begin execute immediate ''''ALTER SYSTEM DISCONNECT SESSION '''''''''||v_first_sid||','||v_first_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_first_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 (chr(9));
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');    
      ELSE
        add_message_line (chr(9));
        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_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');    
      END IF;
    ELSE
      add_message_line ('All valid blocker sessions allready killed. Try kill with OS level command!');
    END IF; 
    IF NOT b_is_script THEN
      s_mail_text := s_mail_text ||  (chr(9)) || chr(10);
      s_mail_text := s_mail_text ||  ('This is automated message so, do not reply to this mail.') || chr(10);
      s_mail_text := s_mail_text ||  ('Regards,')|| chr(10);
      s_mail_text := s_mail_text ||  ('Your auto DBA d.o.o.')|| chr(10);
      s_mail_text := s_mail_text ||  (CHR(9));
      select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into s from dual;
      s_mail_text := s_mail_text ||  ('Zagreb, '||s);
      -- if you run this script from sqlplus then there is no need for this (p_send_mail=FALSE)
      IF p_send_mail THEN
        SYS.UTL_MAIL.send(sender => p_sender,
                  recipients => p_to,
                          cc => p_cc,
                     subject => s_subject,
                     message => s_mail_text,
                   mime_type => 'text/plain; charset=us-ascii');
      END IF;
    END IF;
  ELSE
    dbms_output.put_line ('No blocking sessions found!');
  END IF;
/*
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    */
END;
/

The end

If you get mail now for blocking session and all blocker sessions are killed, then you'll see output like this:

Blocker         Inst  SID     Serial    [sec]   Lock Type     Status       Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction    KILLED
        ZLSA        2   372           126     1128     Exclusive     ACTIVE
        SLPE        2   368            48     6836     Exclusive     ACTIVE
        DRI2        3   237           232     6727     Exclusive     ACTIVE
2. DRI2          3     237         232     6724   Transaction    KILLED
        DRIA        2   355           195      216     Exclusive     ACTIVE

All valid blocker sessions already killed. Try kill with OS level command!

What is right and correct message!

For the end here is how this rewrite version produce result. Here is mail that you'll get in a case of blocking session:

Oracle version: 11.2.0.1.0 (11.1.0.0.0)

Blocker         Inst   SID    Serial    [sec]   Lock Type        Status          Module
------------------------------------------------------------------------------------------------------------------------
 1. DAMIRV        1     20        6       54   Transaction      INACTIVE         SQL*Plus
       DAMIRV      1     141       40       26     Exclusive       INACTIVE        SQL*Plus
 2. DAMIRV        1     141       4       42   Transaction        ACTIVE         SQL*Plus
       DAMIRV      1     144       58       16     Exclusive         ACTIVE        SQL*Plus

---------------  Blocking records information  ---------------
DAMIRV (1 '20,68')
               DAMIRV    1     141,40           26 sec       Exclusive         ACTIVE        SQL*Plus
               SELECT C1 FROM DAMIRV.X WHERE rowid = 'AAAVoXAAGAAAAS1AAA' ;
                       (delete x                                  )

DAMIRV (1 '141,40')
               DAMIRV    1     144,58           16 sec       Exclusive         ACTIVE        SQL*Plus
               SELECT C1 FROM DAMIRV.XX WHERE rowid = 'AAAVoYAAGAAAAS8AAA' ;
                       (delete xx where c1='1'                    )



To kill first from the list, perform:

ALTER SYSTEM DISCONNECT  SESSION '20,68,@1' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '20,68,@1' IMMEDIATE;
--------------------------------------------------------
This is automated message so, do not reply to this mail.
Regards,
Your auto DBA d.o.o.

Zagreb, 21.05.2010 23:39:33

As you see now you can see which row is causing block and statement that is blocked in waiter session-looks promising!

Keep in mind that these two procedures are called in two separate calls, so sometimes second call may shown no blocking records ... and they do not exists in second case call and was existing in first time call.
Cheers!