RE: How to find the exact SQL locking others?

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Mon, 2 Jul 2012 16:51:02 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1BD2F_at_G5W2734.americas.hpqcorp.net>

"You can't" is the answer I have seen Oracle support post on this question in the past. However because the statement is uncommitted I would expect that the cursor will be listed in v$open_cursor for the blocking session. You just have to manually try to determine which open cursor is the one of interest.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger Sent: Monday, July 02, 2012 8:53 AM
To: kamusis_at_gmail.com
Cc: oracle_l
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 - 11:51:02 CDT