Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: UNTO TBS behavior in 9i

Re: UNTO TBS behavior in 9i

From: Hemant K Chitale <>
Date: Sat, 27 Jan 2007 14:53:32 +0800
Message-Id: <>

Answers Indented.

At 06:32 AM Saturday, Ram Raman wrote:
This has evolved into an interesting discussion. I have more questions:

"If you are doing multiple commits during the insert run, each committed batch remains in UNDO for the duration of 2 hours. You should reduce UNDO_RETENTION."

If that is the case, when the current INSERT process needs space it should be able to grab the existing space in UNDO TBS. It should overwrite it even though it is within the UNDO_RETENTION period because oracle has run out of space in UNDO TBS. Correct?

         ===> Normally, undo is retained and Oracle will attempt to get an expired extent for a new transaction / growing transaction

         and if it cannot find an available extent, it will auto-extend the datafile if possible. If it _cannot_ get an extent and cannot extend the undo datafile, then, yes, it will begin to overwrite extents that were used by committed transactions. That is the situation Oracle does not

         actually guarantee that _all_ transactions will be retained for the specified UNDO_RETENTION period. [and a third session may

         get an ORA-1555 because the second session has overwrriten committed undo of a first session].

         In your case , if the datafile could not autoextend, then the session itself would have been allowed to overwrite it's previous undo

"You will RECONSTRUCT a consistent version of The block by applying all "undo vectors" to a current or a consistent version of the block."

If I understand this correctly, this means when an older un-updated version of the block is needed oracle gets the current updated version from memory or disk. Then it applies the undo vectors from the UNDO tbs. If it was a case of DELETE transaction which deleted say 6 rows out of 10 in a block, to produce a consistent version oracle brings the current version of the block which has 4 rows and then applies the change vectors from the UNDO tbs to 'INSERT' the deleted 6 rows to produce a consistent version? Does this mean Oracle stores the 6 deleted rows in UNDO tbs? Or it just stores the ROWIDs in UNDO and selects the old values from the 'deleted spots' in the data block which are still not overwritten?

         ===> Undo for a DELETE is actually the whole row that was deleted. {in order to Rollback a DELETE, Oracle has to perform an INSERT

=> of the whole row, with all column values}

Another clarification: You have mentioned the vectors are applied to a current or consistent version of the block. I can understand the current version, but why take a consistent version and then apply changes again?

         ===> Because the same block may have been updated at different times by different sessions [transactions, actually] -- each one having

=> updated a different row. Therefore, you would have
multiple versions of the same block -- ergo, Oracle must get a

=> _consistent_ version to be able to read the required
rows as of the required SCN. Since undo is actually change vectors

=> [ie corresponding insert/update/delete statements], it
can reapply the changes to the required row {of course, if multiple

=> transactios have committed different record updates, in
order to actually rollback the whole block to disk, Oracle has to check

=> committed and uncommitted rows ! -- but in the case of a
query that wants to read a consistent version of a row, Oracle

=> doesn't have to reapply all changes to all rows in the
block, only to the rows required by that query}.

Hemant K Chitale

Received on Sat Jan 27 2007 - 00:53:32 CST

Original text of this message