You’ll note that this is a session that has been doing quite a lot of work (log_io = db block gets + db block changes, and phy_io = physical reads). I was watching it because I wanted to check that it was only generating undo in a series of very short recursive transactions. (It’s running a coalesce on a very large, very smashed, index).

Interestingly the work done by the recursive transactions for the coalesce was accounted for against the parent transaction, even though (fortunately) the undo block and record usage was not. The used_urec and used_ublk columns can best be described as showing you “currently held” space, rather than “cumulative space used”.

Addendum: with reference to Gary’s comment below – here’s a lovely little script – originally written for 9i, but still working) from Steve Adams for estimating how long it will take for rolling back to complete. Note that it references sys.x_$ktuxe; Steve has a convention of creating “x_” views on the x$ objects when there is no v$ view supplied by Oracle, so x_$ktuxe is just a sys-owned view defined as ‘select * from x$ktuxe’.