It is really really easy to confuse undo and redo in extended conversations like these. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Polarski, Bernard Sent: Thursday, January 25, 2007 9:42 AM To: daniel.fink@xxxxxxxxxxxxxx; adolph.tony@xxxxxxxxxx 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 www.atosorigin.com/be <http://www.atosorigin.com/be> _____ From: Daniel W. Fink [mailto:daniel.fink@xxxxxxxxxxxxxx] Sent: donderdag 25 januari 2007 14:55 To: adolph.tony@xxxxxxxxxx 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 http://www.optimaldba.com/papers/AutomaticUndoInternals.pdf Regards, Daniel Fink NEW pop.tiscali.de 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 Cheers Tony