RE: Holder query in lock

But a single transaction could be made up of multiple DML statements, and multiple statements from the same transaction might have modified the same table, and multiple copies of the same statement might have modified the same 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.

If Oracle were to implement a way to track which SQL_ID was the blocking statement they would actually need to record FOR EVERY ROW MODIFIED by a transaction which execution of which statement blocked that row, and which statement(s) - if any -  was the parent of of the blocking statement.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Yong Huang [dmarc-noreply_at_freelists.org]
Sent: 09 September 2016 14:37
To: Oracle-l Digest Users
Cc: lsantos_at_pobox.com
Subject: Re: Holder query in lock

> But is there a simple way (without any trace) to discover to the
> SQL_ID that locked the rows related to blocker lock?

This is a feature Oracle should have provided a long time ago because so many 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 responsible for transaction to be added to v$transaction") As far as I know, unless you trace or audit the session from at least the point the transaction starts, 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 checked data dictionary for any possible view that contains both SCN and SQL columns, and all events related to transactions.

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


--