Selecting a (Blocking) Locked Row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Selecting a (Blocking) Locked Row

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    Selecting a (Blocking) Locked Row

    How would I select a locked row blocking another session?

    This db is 7.3.4
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    dba_blockers will help to find the blocking sessions.

    blocking rows ....?

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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';
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    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.
    funky...

    "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"

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    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 );

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    RowID_Create Proc is cool, didnt know much abt it.. thx Pando
    funky...

    "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"

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Last edited by jmodic; 02-28-2004 at 11:31 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thank you, this problem is on 7.3.4

    I really apreciate everyone's help and insight. Great forum.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width