Re: How to find the exact SQL locking others?
- From: Leyi Kamus Zhang <kamusis@xxxxxxxxx>
- To: Yong Huang <yong321@xxxxxxxxx>
- Date: Wed, 4 Jul 2012 00:28:08 +0800
Hi Yong
Yes, I agree with you, It definitely helps application troubleshooting.
If Oracle can store all the SQL text somewhere for active transactions
(for example, with Idle event over 5 minutes but still not commit,
just like another type v$session_longops), it will help us a lot.
--
Kamus <kamusis@xxxxxxxxx>
Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org
On Wed, Jul 4, 2012 at 12:11 AM, Yong Huang <yong321@xxxxxxxxx> wrote:
>> I tried to answer this question some time ago and came to the conclusion:
> you can't!
>
> I agree. There's no guaranteed way. This is equivalent to trying to
> disassemble binary executable code back to the exact source code; some
> tokens in the text will definitely be lost. V$open_cursor will not retain
> the original DML unless the original session never executed any other SQL
> after that so there's still a "breakable parse lock" on the cursor. If
> keeping that SQL is so important, the application may need to log it to a
> table or file, or use dbms_shared_pool to "keep" it in shared pool
> (cumbersome and not elegant). I wish Oracle would store these SQLs
> somewhere, maybe store the last one or two in v$transaction. It definitely
> helps application troubleshooting.
>
> Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
