Re: Is using OPTIMAL in RBS ORA-1555 prone?

Syltrem wrote:

> If using OPTIMAL setting in the rollback segments, am I more likely to have
> ORA-1555 snapshot too old?
>
> That is, Oracle will shrink the rollback segment at the start af any new
> transaction, so it may thus drop information required by that other
> long-running process.
>
> Even so, you can never tell when a rbs extent will be reused so how can you
> make 100% sure you will not get this error? Users get it every couple of
> weeks and I can't do an alter session use rollback segment xxxx in this
> application.
>
> Any suggestions appreciated.
>
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
> To reply to myself directly, remove .spammenot from my address
>
>
>
>

OLD faitfull ORA-1555. It is not the optimal that causes the error, it is the frequency (or time)of reuse of a rollback segment that causes it. Asume you have a long running report, some consistent lookups in a rollback segment was just deallocated by optimal, just before you got there - the database engine has no problems finding the data, they are there, well preserved in a deallocated rollback segment. But if it is reopened/reused - you know the drill.
You can (almost) never avoid this error, as long as you dont have infinite rollback segments. But large jobs dooing heavy manipulation would benefit from having their 'own' big rollback segment with many large extents. Then use dbms_transaction.use_rollback_segment(rbsbig) to pin the session to the large rollback segment. Smaler jobs benefit of many rollback segment with many extents but each with less space. But you pay - some on performance. Nothing comes cheap.

/Svend Jensen Received on Mon Feb 18 2002 - 13:34:35 CST