Re: How to drill down on a TX lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 26 Jul 2001 19:56:37 +0100
Message-ID: <996173612.12765.0.nnrp-02.9e984b29@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 - 13:56:37 CDT