Re: commit or not commit inside a cursor loop

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:1e7advsqu9j1k8g4eqikof1r3cv4npl970_at_4ax.com...
> On Wed, 28 May 2003 20:23:27 +0200, "Carsten Saager"
> <carsten_at_saager.org> wrote:
>
> >choice if you cannot enlarge your RBS/UNDO tablespaces for
> >this transaction. The main problem is that Oracle has to maintain the
> >read-consistency cursor so you might a receive (randomly) a 1555 anyway
if
> >other sessions are making changes to the data your cursor relies on.
>
>
> this just absolute crap and hogwash. Rollback segments can and will be
> extended automatically if set up appropiately.
> A transaction is a transaction. Data maintained for read-consistency
> purposes in a transaction will NEVER been overwritten.
> Please don't post such rubbish and nonsense
>

It's not all hogwash. A rollback segment created by a transaction (A) that has !!committed!! is marked as available to be reused. If this rollback segment is being used by another transaction (B) in order to create a read consistent view of some data there is a very real possibility of transaction B suffering a snapshot too old error. To get into this situation, transaction B cannot have locked the rows before transaction A started and must have therefore started after A and in order to need the read consistent view using A's rollback segment(s) must have started before A has finished. Therefore the poster was correct re ora-1555 errors. What you might be thinking of is the read consistent view that relies on rollback segments belonging to the transaction requiring the read consistency. Until this transaction commits it's rollback segments (that it and other transactions may be using for read consistency) cannot be overwritten. Only on commit is the segment(s) marked as available for reuse.

>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu May 29 2003 - 03:04:39 CDT