RE: How to identify the SQL being rolled back?

That's what I was afraid of, thanks. I thought of one approach to find the objects being rolled back - would it make sense to check which segment(s) in v$segment_statistics are getting the most "db block changes" and "physical writes", if it is known that there are no other transactions currently performing updates?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tim Gorman Sent: Friday, August 26, 2005 12:40 PM
To: Oracle-L
Subject: Re: How to identify the SQL being rolled back?

Brandon,

Since any number of SQL statements may have contributed changes to a single transaction, I don't think that there is any V$-view, X$-table, or set of views/tables that record this. Could get pretty large -- difficult to store in a fixed-size SGA.

The only possible mechanism would be Log Miner, where recorded XID information could provide linkage of "do" to "undo" changes.

-Tim

on 8/26/05 1:28 PM, Allen, Brandon at Brandon.Allen_at_OneNeck.com wrote:

> Thanks Jared - nice script. Unfortunately it doesn't tell me what I want to
> know - it still shows only the "ROLLBACK" from v$sql, but I'm trying to find
> *what* is being rolled back - either the original SQL statements, or at least
> the objects that are being rolled back. Any more ideas?
>
>
> SQL> @showtrans
>
> Recur Used Used
> O/S Oracle R-S Space sive No Rbs RBS
> Logical Physical
> User Userid SID Name Trans Trans Undo Blks Recs
> IO Blks IO Blks
> -------- ---------- ----- ------ ----- ----- ----- ------------ ------------
> -------------- --------------
> Current
> Statement
> ------------------------------
> SYSTEM TRIRIGA 307 _SYSSM NO NO NO 138,823 7,804,812
> 104,698,963 2,298,711
> U7$
> ROLLBACK
>
>
> -----Original Message-----
> From: Jared Still [mailto:jkstill_at_gmail.com]
> Sent: Friday, August 26, 2005 12:13 PM
>
> You might try the script below.
>
>
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions and
> other information in this message that do not relate to the official business
> of this company shall be understood as neither given nor endorsed by it.
>
> --
> http://www.freelists.org/webpage/oracle-l
>

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