Re: set a rollback segment for a specific user

If I understand well, all this is to assign a specific rollback segment to a transaction?

Here are other options, equally or more inattractive:

3. Have one rollback segment for all transactions. Contention for segment header is
your friend, but this might work.

4. Oracle assigns rollback segments in round-robin fashion, but according to the number
of active transactions in the segment. Then, to "reserve" the one we need you can simply
start several sessions and leave them running forever.

5. (This is crazy one, but hey). Before starting a transaction, online the rollback segment,
assign to it, and ... immediately offline it. Oracle will block attempts of other transactions
to use this segment, but it should let your transaction complete. So you are left alone using
the rollback segment, maybe with just a few other transactions which might squeeze in before
you offline it. The biggest challenge here is how to offline a rollback segment in the same
transaction. The ALTER ROLLBACK SEGMENT command is a DDL and it will do a commit internally, so your SET statement will not work. To work around it, maybe you
can use an autonomous transaction just to offline a rollback segment. Let others know if
you get this to work.

Maybe some of these will work for the OP? However, I think he just needs a DBMS_TRANSACTION call.

Best regards
AK

PS. Btw, can someone explain to me what cross-posting is? Did I cross-post when I answered
two different emails in the same thread? If so, is there something wrong with it?

Niall Litchfield wrote:

> You have only two options that I am aware of.
>
> 1. Only run your large transactions in off hours having first taken all
> other rollback segments offline. This may well not be attractive to you.
> 2. Size all rollback segments equally and large enough to hold the data
> required for your largest transaction. This would be the preferred advice in
> these days of exceptionally cheap storage.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Steven" <steven.pannell_at_gmx.net> wrote in message
> news:ahrlbh$vi73t$1_at_ID-82797.news.dfncis.de...
> > Hi,
> >
> > I'm wondering if it is possible to set a specific rollback segment for a
> > user so that user only uses the specified rollback segment. My problem is
> > that sometimes I perform large transactions and want to use the large
> > rollback segment. However, I cannot specify the rollback segment using
> "set
> > transaction......" because I am accessing the data through an application.
> >
> > Any help will be appreciated,
> > Regards,
> > Steve.
> >
> >
> >
Received on Sat Jul 27 2002 - 00:38:20 CDT