setting a specific rollback segment

ratikmishra1 · Post by **ratikmishra1** » Thu Mar 17, 2005 3:15 pm

Is it possible to set a specifc rollback segment for a OCI stage?If not possible at the stage level, is there any other way to set the rollback segement for the Oracle transactions?

Thanks
Rati

kcbland · Post by **kcbland** » Thu Mar 17, 2005 3:28 pm

You have before and after SQL statements at your disposal, stored procedure based loading, as well as user-defined SQL. Pick your preference.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Mar 18, 2005 3:10 am

Rollback segments are specified for Oracle db level and not transaction level. So you have to share with other SQL actions.

chulett · Post by **chulett** » Fri Mar 18, 2005 7:26 am

No, you should be able to issue specific 'alter session' commands in the before-sql area to use specific rollback segments if that's really what you need to do.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Mar 18, 2005 8:11 am

Maybe you can look into the following command:

set transaction use rollback segment

but it has its contraints and limitations.

ratikmishra1 · Post by **ratikmishra1** » Fri Mar 18, 2005 10:27 am

Sainath.Srinivasan wrote:Maybe you can look into the following command:

set transaction use rollback segment

but it has its contraints and limitations.

Thanks to all of you for your suggestions.

The before/sql soes not handle the pl/sql driectives. I've tried it before. It works only well for simple SQL statements like delete/insert/update. The last time I tried it could not handle parameters.

"set transaction use " is a pl/sql directive. I was wondering if there is any other way. Will appreciate your ideas.

chulett · Post by **chulett** » Fri Mar 18, 2005 11:09 am

That's why I suggested the "alter session set rollback segment" route. Should work fine in the before-sql area.

jseclen · Post by **jseclen** » Fri Mar 18, 2005 11:14 am

Hi,

I use the next sentence in my job :

ALTER ROLLBACK SEGMENT RBS31 STORAGE ( OPTIMAL 4000M);
SET TRANSACTION USE ROLLBACK SEGMENT RBS31;

This will set in the tab SQL sub tab Before and work without problems.

Now, after the job you must to restore the old rollback settings.

I hope this help.

ratikmishra1 · Post by **ratikmishra1** » Fri Mar 18, 2005 2:08 pm

jseclen wrote:Hi,

I use the next sentence in my job :

ALTER ROLLBACK SEGMENT RBS31 STORAGE ( OPTIMAL 4000M);
SET TRANSACTION USE ROLLBACK SEGMENT RBS31;

This will set in the tab SQL sub tab Before and work without problems.

Now, after the job you must to restore the old rollback settings.

I hope this help.

Looks like it is working. At least did not generate any error.
Thanks again to All of you.