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: Mark W. Farnham <>
Date: Thu, 25 Jan 2007 10:48:27 -0500
Message-ID: <>

It is really really easy to confuse undo and redo in extended conversations like these.

-----Original Message-----

From: []On Behalf Of Polarski, Bernard
Sent: Thursday, January 25, 2007 9:42 AM To:; Cc: oracle-l
Subject: RE: UNTO TBS behavior in 9i

As far as I know, you will find a before image of a block in the case of the first transaction that hits a block in a tablespace set in 'backup mode'. Subsequent change to the block will only generate undo vectors. So there is at least a case where you copy a full before image of a block. However this was true for 8i. I would be surprised if it has changed since as hot backup are rather deprecated in favor or Rman.

Bernard Polarski
Oracle DBA

Direct +32(0)2 690 28 90
Fax +32(0)2 690 27 82

Da Vincilaan 5
1930 Zaventem
Belgium <>

From: Daniel W. Fink [] Sent: donderdag 25 januari 2007 14:55
Cc: oracle-l
Subject: Re: UNTO TBS behavior in 9i

UNDO (both manual in 8i/9i/10g and auto in 9i/10g) entries are not before images of the blocks. They are actually change vectors containing only enough information to undo the change. For example, for an insert the undo vector is basically just the row address and a delete indicator. There are index entries and block header entries (still not complete block images) as well.

For more details, you can read "Automatic Undo Internals" at

Daniel Fink

NEW wrote:
Hi all,

I think the retention setting is a red herring here. I *think* its a real basic issue. Someone posted the suggestion to use the append hint. I reckon this should work. You need to remember that for an insert the UNDO is a copy of the before image and if your inserting into 30G of blocks that have *some space* free, then all of these blocks need to be saved as UNDO.

Maybe your PCTFREE settings need looking at. Has a lot of data been deleted - this would leave a lot of free blocks that will end up in the UNDO. Perhaps you can rebuild the table before the insert ...or even truncate it?

Just ideas, hope they help

-- Received on Thu Jan 25 2007 - 09:48:27 CST

Original text of this message