Determine who locked what row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Determine who locked what row

  1. #1
    Join Date
    May 2001
    Posts
    2
    Greetings,

    Can you find out if a row is locked, who has locked this row?
    I need to display a username when a row in a table is locked..

    Regards

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    There may be two data dictionary views that can help you in your search. The view names are dba_blockers and dba_waiters and are created by running catblock.sql from $OH/rdbms/admin. You can then decipher the session id's from v$session to determine the username.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2001
    Posts
    2
    Thanks for the reply..

    Is it also possible to get the row # or ROWID which is locked..
    This is the scenario..

    There is a table Fred has a primary key emp_no,
    I try to lock the row emp_no = 20 to update by doing 'select for update nowait' but it turned out it is locked I want to know this row is locked by which user inorder to display information to the user..

    How can I do that?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    AFAIK there is no way to find out who is holding lock on a particular row. This information is written in the database block header only, it can not be obtained from any dictionary view or even from any system's fixed table (X$*).
    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
    Apr 2001
    Posts
    51

    Please try using v$locked_object table.

    Hope this helps u.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by miritika

    Please try using v$locked_object table.

    Hope this helps u.
    This only displays which session has a lock on which table, you can't get the information *which row* is locked by which session. There is no way to find out which row is locked by which session, unless you do a database block dump read it from there. But I don't think this is a very usefull way to get this info....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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