RE: How to find the exact SQL locking others?
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: oracle_l <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 2 Jul 2012 16:51:02 +0000
"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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Martin Berger
Sent: Monday, July 02, 2012 8:53 AM
To: kamusis@xxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx>
>
> Visit my blog for more : http://www.dbform.com Join ACOUG:
> http://www.acoug.org
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
