From: Jonathan Lewis <jonathan@jlcomp.demon.co.uk>
To: Oracle-l Digest Users <oracle-l@freelists.org>
Subject: RE: Holder query in lock
Thread-Topic: Holder query in lock
Date: Fri, 9 Sep 2016 13:42:06 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9015033B8AC@exmbx05.thus.corp>
References: <1986011603.1839367.1473428242320.ref@mail.yahoo.com>,<1986011603.1839367.1473428242320@mail.yahoo.com>
In-Reply-To: <1986011603.1839367.1473428242320@mail.yahoo.com>
Accept-Language: en-GB, en-US
Content-Language: en-GB
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jonathan@jlcomp.demon.co.uk

=0A=
But a single transaction could be made up of multiple DML statements, and m=
ultiple statements from the same transaction might have modified the same t=
able, and multiple copies of the same statement might have modified the sam=
e table, and recursive statements (e.g. from "on delete cascade") might be =
responsible for the actual block rather than the statement that the client =
program ran.=0A=
=0A=
If Oracle were to implement a way to track which SQL_ID was the blocking st=
atement they would actually need to record FOR EVERY ROW MODIFIED by a tran=
saction which execution of which statement blocked that row, and which stat=
ement(s) - if any -  was the parent of of the blocking statement.=0A=
=0A=
=0A=
Regards=0A=
Jonathan Lewis=0A=
http://jonathanlewis.wordpress.com=0A=
@jloracle=0A=
=0A=
________________________________________=0A=
From: oracle-l-bounce@freelists.org [oracle-l-bounce@freelists.org] on beha=
lf of Yong Huang [dmarc-noreply@freelists.org]=0A=
Sent: 09 September 2016 14:37=0A=
To: Oracle-l Digest Users=0A=
Cc: lsantos@pobox.com=0A=
Subject: Re: Holder query in lock=0A=
=0A=
> But is there a simple way (without any trace) to discover to the=0A=
> SQL_ID that locked the rows related to blocker lock?=0A=
=0A=
This is a feature Oracle should have provided a long time ago because so ma=
ny people have asked for it. I filed a feature request. (For those who can =
see, it is: "Severity 4 SR 3-12200129251 : Request for ID of SQL responsibl=
e for transaction to be added to v$transaction") As far as I know, unless y=
ou trace or audit the session from at least the point the transaction start=
s, or using LogMiner (as Stefan and Dominic suggested), there's no reliable=
 way to find the initial DML SQL that creates the transaction. I have check=
ed data dictionary for any possible view that contains both SCN and SQL col=
umns, and all events related to transactions.=0A=
=0A=
Yong Huang=0A=
--=0A=
http://www.freelists.org/webpage/oracle-l=0A=
=0A=
=0A=
--
http://www.freelists.org/webpage/oracle-l