Re: How to find the exact SQL locking others?

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_at_gmail.com>

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_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l