Re: How to drill down on a TX lock

In v$session there are some columns that may be useful depending on what version you are running.

ROW_WAIT_OBJ#
NUMBER
ROW_WAIT_FILE#
NUMBER
ROW_WAIT_BLOCK#
NUMBER
ROW_WAIT_ROW#
NUMBER Query v$session and the user that is waiting on the row lock will have these values populated.

Eg Session 1
DATA0025-SCOTT_at_swtest > update test1 set s1 = 'HELLOP' where n1 = 5600; 1 row updated.

Session 2.
DATA0025-SCOTT_at_swtest > update test1 set s1 = 'HELLOP' where n1 = 5600; // SESSION IS BLOCKED SESSION with access to V$SESSION
  1* select username, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# from v$session where username = 'SCOTT' DATA0025-SYS_at_swtest > /

USERNAME                       ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK#

------------------------------ ------------- -------------- --------------- 
-------------
SCOTT                                     -1              0               0
0
SCOTT                                  24638              5             130

From here I am guessing there is some way to convert these values into a rowid to find the row you are looking for.

HTH
Scott Watson.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:996173612.12765.0.nnrp-02.9e984b29_at_news.demon.co.uk...
>
> You could play silly games with block dumps,
> but for a small table you might do a bit of pl/sql
> like:
>
>
> for r1 in (select pk from t1) loop
> begin
> select 'x' from t1 where pk_col = r1.pk for update nowait
> exception
> when oracle error 54 then
> report pk
> end;
> end;
> /
>
>
> (Error 54 is the one about a resource being busy when
> requested with a nowait - there may be a predefined
> exception for it, but I think you have to declare and init
> your own).
>
> This will list all the locked rows - although it does not
> tell you which transaction (hence session) is locking
> them.
>
> If you want to use block dumps, get the transaction
> id from v$transaction, dump the blocks, look for blocks
> with an itl entry that has an XID matching the entry
> in v$transaction, then look for rows in the block where
> the lock byte it set to the ITL number of that transaction.
>
>
>
>
>
> --
> Jonathan Lewis
>
> Host to The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
> See http://www.jlcomp.demon.co.uk/book_rev.html
>
> Seminars on getting the best out of Oracle
> See http://www.jlcomp.demon.co.uk/seminar.html
>
> Screensaver or Lifesaver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
>
>
>
> Doug C wrote in message ...
> >I have a feeling this might be somewhat complicated and involve doing
 block
> >dumps. Irregardless.. can anyone provide an example? Let's say I've got
 4
 rows
> >in a table locked exclusively with a TX lock. What must I do to
 ascertain
 which
> >rows are locked. Dumping every block in the table is acceptable in this
> >situation because the table is pretty small. Any suggestions, examples?
> >
> >Thanks,
> >D
>
>
Received on Thu Jul 26 2001 - 16:11:29 CDT