RE: If undo fills will it roll back updates/inserts?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Guillermo Alan Bort'" <cicciuxdba@xxxxxxxxx>
- Date: Fri, 2 Jul 2010 16:41:46 -0400
Paul Drake reminded me offline that resumable settings also affect this.
Thanks Paul
_____
From: alanbort@xxxxxxxxx [mailto:alanbort@xxxxxxxxx] On Behalf Of Guillermo
Alan Bort
Sent: Friday, July 02, 2010 3:54 PM
To: mwf@xxxxxxxx
Cc: po04541@xxxxxxxxx; Oracle L
Subject: Re: If undo fills will it roll back updates/inserts?
Yes, Oracle will throw a "unable to extend undo segment by ###" and the
process will fail, which means the transaction will be rolled back. If you
want to avoid it, try commiting every frew thousand rows.
hth
Alan.-
On Fri, Jul 2, 2010 at 4:45 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Check your retention guarantee and time. If I recall correctly, Oracle will
throw away unguaranteed and/or expired flashback and undo (that is no longer
pinned by an active transaction) before it will give up on an active
transaction. Also if I recall correctly, it will fill up only the allocated
tablespace with unexpired flashback, but is lazy about throwing it away.
That seems smart to me.
But if push comes to shove, meaning Oracle cannot throw anything away and it
cannot extend, then I believe it still rolls that transaction back, rather
than hanging and waiting for more space (as it does for archive destination
full).
The details of your exact settings deserve testing before you rely on this,
and it may vary somewhat by point release during the releases when these
features were introduced. I did minimal testing in 11.1, I think, and it
seems to work as described, but I did not do a full case analysis.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of patrick obrien
Sent: Friday, July 02, 2010 3:27 PM
To: Oracle L
Subject: If undo fills will it roll back updates/inserts?
Oracle Admins,
if my undo fills up, will it roll back updates and inserts?
I've got a 170Gig database, I increased my SGA and now my undo has grown 20
gigs in a week. I don't want to turn on auto extend.
if this data warehouse tablespace fills up during my nightly cycle will it
rollback my updates/inserts and kill this job?
I looked at my undo and its 80% flashback data, 3 days worth of flashback,
and yet my flashback is set to only 7 hours.
Any ideas,
thank you,
Patrick.
Other related posts:
