Re: using a Rollback Segment for all transactions

dev_at_null wrote:

> On Wed, 20 Nov 2002 19:31:16 GMT, Daniel Morgan <dmorgan_at_exesolutions.com>
> wrote:
>
> > bertrand Quatromme wrote:
> >
> > > I work on a datawarehouse.
> > > I have many transactions which works on multi-threaded sessions.
> > >
> > > I try to have all my transaction use a rollback segment which is not
> > > always the default one.
> > >
> > > Actually I have only found the following command :
> > >
> > > set transaction use rollback segment RBS_DSS;
> > >
> > > But Can I do it at the session level ? or for the particular user which
> > > runs these transactions (at the time I create this user for example like
> > > when I choose the default tablespace) ?
> > >
> > > Thanks for all help.
> > >
> > > --
> > > bertrand
> >
> > It would be a good thing if you just forgot about SET TRANSACTION. If your
> > rollback segments are properly sized and present in sufficient number about
> > all you can do with SET TRANSACTION is make a mess of things.
> >
> > But in answer to your question no you can not assign a user to a rollback
> > segment. SET TRANSACTION must be issued at the beginning of each session and
> > again immediately following every COMMIT and ROLLBACK.
> >
> > Daniel Morgan
>
> Hmm, curious. I was under the impression that set transaction had its uses. For
> example, in a system that usually saw a lot of oltp with the occasional bulk
> load. Isn't the general advice to set up a load of small rollback segments for
> the oltp and have a wacking great big one for the bulk work?

General advice is often bad advice. What happens if that one rollback segment is offline? What if it is currently being used by another transaction? What if it is dropped? The list of possible issues is quite long.

Size all rollback segments large enough to handle the largest transaction on the system and then forget the crashes and the maintenance nightmares.

Daniel Morgan Received on Thu Nov 21 2002 - 17:40:31 CST