Oracle Rollback Segments and ADO.NET

Question

I am using an ancient version of Oracle (8.something) and my ADO.NET application needs to do some fairly large transactions. Large enough to not fin in our small rollback segments. Now we have a large rollback segment as well but it is not used by default.

Oracle has a command to select the rollback segment to be used (SET TRANSACTION USE ROLLBACK SEGMENT MY_ROLLBACK_SEGMENT) but it needs to be the first command issued in the transaction. Unfortunately, it seems that ADO.NET issues some other commands at the beginning of a transaction since issuing this command right after .BeginTransaction() throws an error about SET TRANSACTION not being the first command.

I am sure I am not the only one who faced this issue. How do you solve it or how would you get around it?

Thanks

Answer 1

If this is a 'one-off' requirement then one solution is to put the other rollback segments offline while you run your transactions then put them online when you've finished;

ALTER ROLLBACK SEGMENT <name> OFFLINE;

ALTER ROLLBACK SEGMENT <name> ONLINE;

Otherwise make all the rollback segments the same size.

Answer 2

I have absolutely no way of testing this, but you could try issuing a save point before your set transaction statement, e.g.

SAVEPOINT use_big_rbs;

SET TRANSACTION USE ROLLBACK SEGMENT big_rbs;

UPDATE ...

...

Answer 3

Hi

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

I assume this is a procedure / function.

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

Regards K