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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
OK, here's what I have so far, how do I make the leap to getting the rowid?
from dba_data_files a,
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
/* Will not be applicable for Inserts */
S.Sid = 'My_Locked_Sid' and
S.ROW_WAIT_OBJ# != -1
/* This part is to limit the search criteria in the table */
* My_Where_Condition_From_V$SqlText joined with My_table *
This may be helpful am not sure..
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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
where sid = &locker;
column objname new_value objname noprint
select owner ||'.'|| object_name objname
where object_id = &wait_obj;
set termout on
prompt &username with sid &blocker is blocking following data in &objname
where rowid = dbms_rowid.rowid_create( 1, &wait_obj, &wait_file, &wait_block, &wait_row );
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: