Re: HELP: Get ORA-1555 rollback too small

"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, 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:04:45 CST