Re: Using SET TRANSACTION USE ROLLBACK SEGMENT

A copy of this was sent to Dipen Kotecha <dkotecha_at_ford.invalid> (if that email address didn't require changing) On Wed, 24 Nov 1999 15:09:30 +0100, you wrote:

>We have large overnight load jobs that need to use a particularly large
>rollback segment to avoid the dreaded 'ORA-01555: snapshot too old:
>rollback segment number %s with name "%s" too small' problem.
>
>I am planning to use SET TRANSACTION USE ROLLBACK SEGMENT to specify
>which rollback segment the job should use, but I have some questions
>before I do so :
>
>1) Do I have to use the SET TRANSACTION USE ROLLBACK SEGMENT command for
>every transaction or can I just set the rollback segment at the start of
>the job ?

every.

>
>2) We also have some batch jobs (that may or may not run at the same
>time) and these also generate rollback. Do I have to make sure that
>these jobs do NOT use the rollback segment reserved for the load job(s)
>using the SET TRANSACTION USE ROLLBACK SEGMENT but referring to a
>different rollback segment, or will ORACLE automatically avoid the
>rollback segment used for the load job.
>

no. it'll use the rollback segment used by the load job but thats OK.

>Also more generally :
>
>3) How does Oracle decide which rollback segment to use?
>

round robin mostly -- other factors can influence (how many using, can it be used and so on)

>4) Can we have more than one process writing to the same rollback
>segment at the same time?
>

absolutely.

>Any help will greatly appreciated.
>

I'm afraid that having one big rollback segment for your load job may not help you to avoid the 1555. If your load job reads data the other jobs are updating -- it is the fact that THEIR rollback is too small, not its rollback.

>Thanks in advance,
>
>
>Dipen Kotecha

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 24 1999 - 08:55:33 CST