Which Oracle session is blocking on which row

This script will help a DBA see which session is blocking another and on which row.

Here is a script that could help a DBA to see which session is blocking another and on which row exactly.

Script to list all blocking lock:

set serverout on size 1000000
set lines 132
declare
  cursor cur_lock is
         select sid,id1,id2,inst_id, ctime
           from gv$lock
          where block = 1;
  vid1       number;
  vid2       number;
  cursor cur_locked is
         select sid, inst_id, ctime
           from gv$lock
          where id1 = vid1
            and id2 = vid2
            and block <> 1;
  vlocks     varchar2(30);
  vsid1      number;
  vobj1      number;
  vfil1      number;
  vblo1      number;
  vrow1      number;
  vrowid1    varchar2(20);
  vcli1      varchar2(64);
  vobj2      number;
  vfil2      number;
  vblo2      number;
  vrow2      number;
  vrowid2    varchar2(20);
  vcli2      varchar2(64);
  vobjname   varchar2(30);
  vlocked    varchar2(30);
  ctim1      number;
  ctim2      number;
begin
dbms_output.put_line('=====================================================');
dbms_output.put_line('Blocking lock list.');
dbms_output.put_line('=====================================================');
dbms_output.put_line('Block / Is blocked         SID        INST_ID OBJECT                         TIME(secs) ROWID              CLIENT_IDENTIFIER');
dbms_output.put_line('-------------------------  ---------  ------- ------------------------------ ---------- ------------------ -----------------');
  for c1 in cur_lock loop
      vid1 := c1.id1;
      vid2 := c1.id2;
      select username,sid,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,client_identifier
                 into vlocks,vsid1,vobj1,vfil1,vblo1,vrow1,vcli1 
                 from gv$session where sid = c1.sid and inst_id = c1.inst_id;
      if vobj1 = -1 then
         vobjname := 'UNKNOWN';
      else
         select name into vobjname from sys.obj$ where obj# = vobj1;
         select decode(vrow1,0,'MANY ROWS',dbms_rowid.rowid_create(1, vobj1, vfil1, vblo1, vrow1)) into vrowid1 from dual;
      end if;
      dbms_output.put_line(rpad(vlocks,25) || ' ' ||
                           to_char(vsid1,'999999999') || ' ' ||
                           to_char(c1.inst_id,'9999999') || ' ' ||
                           rpad(vobjname,30) || ' ' ||
                           to_char(c1.ctime,'999999999') || ' ' || rpad(vrowid1,18) || ' ' || vcli1);
      for c2 in cur_locked loop
          select username, row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
            into vlocked, vobj2, vfil2, vblo2, vrow2 
            from gv$session where sid = c2.sid and inst_id = c2.inst_id;
          if vobj2 = -1 then
             vobjname := 'UNKNOWN';
          else
             select name into vobjname from sys.obj$ where obj# = vobj2;
             select decode(vrow2,0,'MANY ROWS',dbms_rowid.rowid_create(1, vobj2, vfil2, vblo2, vrow2)) into vrowid2 from dual;
          end if;
          dbms_output.put_line(chr(9) || '\--> ' || rpad(vlocked,12) || ' ' || 
                 to_char(c2.sid,'999999999') || ' ' || 
                 to_char(c2.inst_id,'9999999') || ' ' || rpad(vobjname,30) || ' ' ||
                 to_char(c2.ctime,'999999999') || ' ' || rpad(vrowid2,18) || ' ' || vcli2 ) ;
      end loop;
  end loop;
commit;
end;
/

This was last published in June 2006

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close