Re: commit or not commit inside a cursor loop

Carsten Saager wrote:

> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> schrieb im Newsbeitrag
> news:1j3cdvkad8aqg2rruaj45ejelpk64mm4dr_at_4ax.com...
> > You obviously didn't read the hogwash. He *RECOMMENDED* committing *IN
> > THE LOOP* to *AVOID ORA-1555*.
> > now that is ABSOLUTELY PLAIN CRAP.
> > You, not reading the hogwash, describe why his hogwasg CAN"T WORK
>
> Maybe I have not been able to make my point as clear as I should:
>
> I did not recommend to commiting in the loop to avoid ORA-1555. I explicitly
> stated that commiting in a loop my LEAD to 1555 for the reason Andy
> explained.
>
> My point was that there are two reason for commiting in a loop:
> 1. You regard the actions in the body as transactions, so you have to commit
> them in case of success (and free locks on the data associated with the
> transaction) - Of course the argued situation might occur then...
> 2. On some systems disk space is a scarce resource. Given such a situation
> your datafiles cannot extend to fulfill your need for rollback-memory.
>
> In any case, rethink the tone of your posts. Just signing a post with
> "senior dba" doesn't promote you to an arc-angel:-)

I take exception. I have not in more than a decade of Oracle ever seen anything done in a loop considered separate logical transactions and the solution to your second point is to buy more hard disk not to kludge together something that begs for ORA-01555.

I understand the reasoning behind your statements. But then I understand lots of things people try to do that don't work or don't work well.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)