RE: undo block number
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Mar 2015 08:41:32 +0000
On the other hand, for small transactions you might be lucky:
used_ublk is the number of undo blocks used by the transaction (so far).
ubafil etc report the current block
start_ubafil report the first undo block used
If used_ublk <= 2 then you've got the lot.
Another point on the UBA from the data block: it will only identify the undo
block describing how to reverse the last change made to the block by the
transaction. If, for example, you've updated many rows through an indexed
access path that jumped all over the table, then this block might hold many
rows that were updated at different moments in the transaction, and each row
might be associated with a different undo block.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Chitale, Hemant K [Hemant-K.Chitale@xxxxxx]
Sent: 11 March 2015 07:47
To: kaygopal@xxxxxxxxx
Cc: ORACLE-L
Subject: RE: undo block number
That wouldn’t identify all the undo blocks for the transaction, would it ?
Because a transaction can be using many Undo Blocks. But the transaction has
only 1 entry in v$transaction. So not all the Undo blocks would be visible.
Unless one knew how to walk the chain of undo blocks, beginning with dumping
the first undo block, reading the trace, finding the next undo block for the
transaction, then dumping it and so on.
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of K Gopalakrishnan
Sent: Tuesday, March 10, 2015 9:39 PM
To: cure@xxxxxxxxxxxxx
Cc: Oracle Mailinglist
Subject: Re: undo block number
Paul--
Look for UBA-BLK/FIL/REC values in the V$transaction and dump those blocks for
the transaction id.. XID is USN.SLOT#.WRAP#.
-Gopal
On Tue, Mar 10, 2015 at 8:21 AM, Paul Harrison
<cure@xxxxxxxxxxxxx<mailto:cure@xxxxxxxxxxxxx>> wrote:
Hi All,
How does one find the undo block number associated with a transaction? I want
to dump the undo block to see the contents?
Below is the block number associated with the data block but not sure how to
find the undo block number for this transaction. Is the “Uba” of the
Transaction table the undo block number?
select
2 dbms_rowid.rowid_relative_fno(rowid) File#,
dbms_rowid.rowid_block_number(rowid) Block#
4 from t1
5 ;
FILE# BLOCK#
---------- ----------
1 93569
1 93569
1 93569
1 93569
Thanks,
Paul
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
Other related posts:
