Re: How to find the exact SQL locking others?

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 2 Jul 2012 06:21:50 -0700 (PDT)
Message-ID: <1341235310.56696.YahooMailNeo_at_web160903.mail.bf1.yahoo.com>

V$SESSION_BLOCKERS provides the information to identify the blocking session as it reports the sid and serial# of blocked sessions as well as the sid and serial# of the blocking session.  This information along with SQL_ID from V$SESSION and the V$SQLAREA view would, as far as I can tell, report on the blocking SQL statement.  So it appears that Oracle does keep track of this information (if not directly then indirectly). 

David Fitzjarrell

---

From: Martin Berger <martin.a.berger_at_gmail.com> To: kamusis_at_gmail.com
Cc: oracle_l <Oracle-L_at_freelists.org> Sent: Monday, July 2, 2012 6:53 AM
Subject: Re: How to find the exact SQL locking others?

Kamus,
I tried to answer this question some time ago and came to the conclusion: you can't!

Of course it's hard to prove the absence of a possibility. but I just tried to imagine if (and where) Oracle would need this information? It just don't need it!
The whole uncommitted thing is handled by a transaction. For any consistent get in buffer cache, the transaction is sufficient; also for rollback (or commit).
At the moment a cursor is closed, no one cares about it anymore.

Even in log miner there is the transaction and the changes, but afaik not the exact statement?

If you find any reason the RDBMS needs this information, let's dig into it. Otherwise this feature must be part of any kind of enhancement request (either filed in the past or future) otherwise it does not exist in the code.

sorry for this kind of answer,
Martin

On Mon, Jul 2, 2012 at 9:45 AM, Leyi Kamus Zhang <kamusis_at_gmail.com> wrote:

> 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
>
>
>

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

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

Received on Mon Jul 02 2012 - 08:21:50 CDT