Using SET TRANSACTION USE ROLLBACK SEGMENT

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 ?
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.

Also more generally :

3) How does Oracle decide which rollback segment to use?

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

Any help will greatly appreciated.

Thanks in advance,

Dipen Kotecha Received on Wed Nov 24 1999 - 08:09:30 CST