Selecting a (Blocking) Locked Row

[KenEwald]

How would I select a locked row blocking another session?

This db is 7.3.4

[rajabalachandra]

dba_blockers will help to find the blocking sessions.

blocking rows ....?

[KenEwald]

Yup, The rowid(s) of the row(s) being locked. I'd like to look at the row to figure out who's row it is, then wake that person up.

Maybe the SQL used to lock the row blocking other sessions.

[jmodic]

If a session is actually waiting on a locked row, you can identify on which row (it's ROWID) it is waitin for by looking in combination of V$SESSION's columns ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# of that waiting session.

[KenEwald]

OK, here's what I have so far, how do I make the leap to getting the rowid?

select
b.machine host,
b.username username,
b.server,
b.osuser os_user,
b.program program,
a.tablespace_name ts_name,
row_wait_file# file_nbr,
row_wait_block# block_nbr,
c.owner,
c.segment_name,
c.segment_type
from dba_data_files a,
v$session b,
dba_extents c
where b.row_wait_file# = a.file_id
and c.file_id = row_wait_file#
and row_wait_block# between c.block_id
and c.block_id + c.blocks - 1
and row_wait_file# <> 0
and type='USER';

[abhaysk]

Select 
/* Will not be applicable for Inserts */
  T.Rowid  ,
  T.* 
From 
  My_Table T
Where 
  (
   DBMS_ROWID.ROWID_RELATIVE_FNO(T.Rowid), 
   DBMS_ROWID.ROWID_BLOCK_NUMBER(T.Rowid), 
   DBMS_ROWID.ROWID_ROW_NUMBER(T.Rowid)
  )
                 IN
  (
   Select
     S.ROW_WAIT_FILE#     ,
     S.ROW_WAIT_BLOCK#    ,
     S.ROW_WAIT_ROW#
   From
     V$session S
   Where
     S.Sid = 'My_Locked_Sid' and
     S.ROW_WAIT_OBJ# != -1
  )                                   And
/* This part is to limit the search criteria in the table */
  Rowid In
  (
   Select
     A.Rowid
   From
     A.My_Table, .... 
   Where
     * My_Where_Condition_From_V$SqlText joined with My_table *
  )

This may be helpful am not sure..

Abhay.

[pando]

set echo off
set feed off
set verify off

select a.sid BLOCKER, ' blocks ' || b.sid BLOCKED
  from v$lock a, v$lock b
 where a.id1 = b.id1
   and a.id2 = b.id2
   and a.block = 1 
   and b.request > 0;

accept blocker prompt 'Specify blocker sid: '
accept locker prompt 'Specify blocked sid: '

set termout off
column username new_value username
column wait_obj new_value wait_obj
column wait_file new_value wait_file
column wait_block new_value wait_block
column wait_row new_value wait_row
select sid, 
       username,
       row_wait_obj# wait_obj, 
       row_wait_file# wait_file, 
       row_wait_block# wait_block, 
       row_wait_row# wait_row
  from v$session 
 where sid = &locker;
column objname new_value objname noprint
select owner ||'.'|| object_name objname
  from dba_objects
 where object_id = &wait_obj;
set termout on

prompt 
prompt &username with sid &blocker is blocking following data in &objname
select *
  from &objname
 where rowid = dbms_rowid.rowid_create( 1, &wait_obj, &wait_file, &wait_block, &wait_row );

[abhaysk]

RowID_Create Proc is cool, didnt know much abt it.. thx Pando

[jmodic]

DBMS_ROWID package? Wasn't the original question about Oracle 7.3.4 ?

Ken, as I said V$SESSION's ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# uniquely identify the row being locked for - in pre-Oracle8 database those three components constitute the ROWID. The rowid is constituted like this: BLOCK.ROW.FILE. When converted to character type, each of the three parts is converted to hexadecimal notation with leading zeroes, with string lengths 8, 4, 4 respectively. So for example rowid 000000A5.0000.0001 means that the this is a first row ('0000', row numbering starting from 0!) of the 165th block ('000000A5') in the first datafile ('0001'). So onec you write your own DECIMAL_TO_HEX function (or find one on the internet - Tom Kyte used to have a nice and universal one), all you need to get the rowid of the blocking row is:

LPAD(decimal_to_hex(b.row_wait_block#),8,'0') || '.' ||
LPAD(decimal_to_hex(b.row_wait_row#),4,'0') || '.' ||
LPAD(decimal_to_hex(b.row_wait_file#),4,'0')

If you want to convert that to ROWID datatype, simply use CHARTOROWID() built-in function.

[KenEwald]

Thank you, this problem is on 7.3.4

I really apreciate everyone's help and insight. Great forum.