Re: HELP: Get ORA-1555 rollback too small

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:PozI9.92706$g9.257709_at_newsfeeds.bigpond.com...
>
> "Ban Spam" <ban-spam_at_operamail.com> wrote in message
> news:Xns92DD86F35F649SunnySD_at_68.6.19.6...
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in
> > news:_zaI9.91867$g9.256355_at_newsfeeds.bigpond.com:
> >
> > >>
> > >> You can commit at whatever rate your heart desires, just keep in mind
> > >> that whenever you do this you increase risk of 1555 for some other
> > >> long running query against the same table.
> > >
> > > One more time, then. Commiting, in and of itself, does *not* increase
> > > the risk of 1555.
> > >
> > > Witness 9i, where the right to over-write rollback is completely and
> > > formally divorced from the commit, thanks to undo_retention. In
> > > earlier versions, sure -a commit meant you were free to over-write.
> > > But even in those versions, it didn't mean you *had* to over-write.
> > >
> > > Only the size of the rollback segment (or the lack of it) means that.
> >
> > Yes, but consider the following scenario.
> > Assume I need to UPDATE a million records in a single table.
> > Assume that the RBS is large enough to handle all 1,000,000 records.
> > If only 1 COMMIT is issued at the very end, Oracle GUARENTEES that
> > no ORA-01555 error gets generated. If a COMMIT is issued every 1,000
> > records, getting an ORA-01555 is possible and there is NOTHING you can
> > to 100% ensure that an ORA-01555 won't occur.
>
> Run that one past me again. The rollback segment is big enough to hold the
> entire transaction (and any other transactions which are taking place at
the
> same time, which is standard advice).
>
> Then no, you won't over-write prior undo, and you won't get ORA-1555s as a
> result. And I don't care how often you commit in the meantime: you still
> won't get an ORA-1555 as a result of over-writing prior redo,

(I meant of course prior *undo*)

:(
HJR
> because the
> size of the segment is such that you won't be over-writing anything.
>
> Whether or not you get a 1555 as a result of the delayed block cleanout
(if
> that is what you are getting at) is moot, and is an additional point that
> needs to be taken into account, no doubt... but it's not what this
> discussion was about.
>
> I must be missing your point, I think.
>
> HJR
>
>
Received on Sat Dec 07 2002 - 22:07:42 CST