How to find the exact SQL locking others?

From: Leyi Kamus Zhang <kamusis_at_gmail.com>
Date: Mon, 2 Jul 2012 15:45:06 +0800
Message-ID: <CAPtFprr9ZqKvKQiv=ZxQ6eRAKxBdRsghxs+gHKznOmO7+QwyRA_at_mail.gmail.com>

Hi Lists
Maybe the question is not so easy as it looked from subject.

Session 1:
SQL> update t set n=2 where n=1;
<<==== no commit here
SQL> select sysdate from dual;
SQL> select table_name from tabs;
<<==== run any SQL that you want, to age out the SQL_ID and PREV_SQL_ID in v$session

Session 2:
SQL> update t set n=3 where n=1;
<<===== will hang in wait for "TX-row lock contention"

My question is: How to find the exact SQL "update t set n=2 where n=1" was issued in session 1?

I tried:

1. from v$active_session_history, the SQL is not captured by sampling
2. from v$open_cursor, no luck
3. oradebug dump processstate, no SQL text
4. oradebug hanganalyze, no SQL text

--

Kamus <kamusis_at_gmail.com>

Visit my blog for more : http://www.dbform.com Join ACOUG: http://www.acoug.org

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 02 2012 - 02:45:06 CDT