Re: How to find the exact SQL locking others?

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