Re: Using Set transaction use rollback segment foo

Hi Allen,

    If you are only using the big rollback segment for a limited number of operations it may be possible to alter it offline when it is not needed. This will require a "wrapper" around your transaction like so,

    ALTER ROLLBACK SEGMENT rbs_big ONLINE;     SET TRANSACTION USE ROLLBACK SEGMENT rbs_big;     (transaction code here)
    ALTER ROLLBACK SEGMENT rbs_big OFFLINE;

It isn't guaranteed to work since it is possible that another user might grab the rollback segment between the time it comes online and the set transaction. If this is a concern one way to minimize this possibility is to create several smaller rollback segments and leave them online all of the time.

regards

Jerry

Allen wrote in message <6smqan$587$1_at_Starbase.NeoSoft.COM>...
>I have a stored procedure that executes 8 large inserts that require a 1.5
>gb rollback segment. Before each statement, I execute:
>
>set transaction use rollback segment rbs_big;
>
>after each insert, I commit and then do another set transaction.
>
>The problem: Sometimes Oracle does not give me the rollback segment
>requested. In addition, it does not fire an error exception when this
>occurs. I don't know of the failed set until the following insert fails
>from having too small of a rollback segment.
>
>Is there any trick that ensures that I get my requested rollback segment?
>This is a batch program and is only run by one user. There are
>approximately 30 - 60 other users on the machine at once.
>
>Thanks.
Received on Fri Sep 04 1998 - 07:16:10 CDT