Page 1 of 1
setting a specific rollback segment
Posted: Thu Mar 17, 2005 3:15 pm
by ratikmishra1
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
Posted: Thu Mar 17, 2005 3:28 pm
by kcbland
You have before and after SQL statements at your disposal, stored procedure based loading, as well as user-defined SQL. Pick your preference.
Posted: Fri Mar 18, 2005 3:10 am
by Sainath.Srinivasan
Rollback segments are specified for Oracle db level and not transaction level. So you have to share with other SQL actions.
Posted: Fri Mar 18, 2005 7:26 am
by chulett
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.
Posted: Fri Mar 18, 2005 8:11 am
by Sainath.Srinivasan
Maybe you can look into the following command:
set transaction use rollback segment
but it has its contraints and limitations.
Posted: Fri Mar 18, 2005 10:27 am
by ratikmishra1
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.
Posted: Fri Mar 18, 2005 11:09 am
by chulett
That's why I suggested the "alter session set rollback segment" route. Should work fine in the before-sql area.
Posted: Fri Mar 18, 2005 11:14 am
by jseclen
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.
Posted: Fri Mar 18, 2005 2:08 pm
by ratikmishra1
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.