Clever Geek Handbook

Oracle and ADO.NET Rollback Segments

I am using an ancient version of Oracle (8.something) and my ADO.NET application needs to do some pretty large transactions. Large enough not to float in our small retrace segments. We now have a large rollback segment, but it is not used by default.

Oracle has a command to select the rollback segment to use ( SET TRANSACTION USE ROLLBACK SEGMENT MY_ROLLBACK_SEGMENT

), but it must be the first command issued in a transaction. Unfortunately, it looks like ADO.NET is issuing some other commands at the start of a transaction, from the time that command was issued right after .BeginTransaction () throws an error that SET TRANSACTION is not the first command.

I'm sure I'm not the only one who faced this problem. How do you solve it or how will you get by?

thank

0


source to share


3 answers


If this is a "one-time" requirement, then one solution is to disable the rest of the rollback segments when you start your transactions and then post them online when you're done;

ALTER ROLLBACK SEGMENT <name> OFFLINE;

ALTER ROLLBACK SEGMENT <name> ONLINE;


Otherwise, all rollback segments will be the same size.

+1


source


I can't test this on Oracle 8, but on newer versions, you can explicitly start a new transaction by releasing a commit and then changing the rollback segment.

I assume this is a procedure / function.



begin
commit;
SET TRANSACTION USE ROLLBACK SEGMENT UNDOTBS1;
--Your code here
end;

Relationship K

+1


source


I have absolutely no way to test this, but you could try posting a savepoint in front of your established transaction statement, for example.

SAVEPOINT use_big_rbs;

CONFIGURING A DEAL USE THE ROLLBACK SEGMENT big_rbs;

UPDATE ...

...

0


source






More articles:


All Articles