-
Problem setting transaction to a rollback segment
Hi
I have a problem with a script that I am trying to run that will update millions of rows.
In order to offset the chance of blowing out the rollback segments, I created a very large public rollback segment, set the transaction to use it, and then started the script.
However, it uses all of the other rollback segments, and then when it runs out of rollback segments, it fails.
Is there anyway around this? I have never had this particular problem setting a specific rollback segment to a specific transaction.
Thanks
-
Try this
SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;
Jigar
"High Salaries = Happiness = Project Success."
-
That is the syntax I used:
set transaction use rollback segment LARGE_RBS01;
-
Before SET TRANSACTION, Did you make LARGE_RBS01 online like :
ALTER ROLLBACK SEGMENT LARGE_RBS01 ONLINE;
SET TRANSACTION USE ROLLBACK SEGMENT LARGE_RBS01;
Hope that Helps you.
-- Dilip
-
Re: Problem setting transaction to a rollback segment
Originally posted by hoffmak
However, it uses all of the other rollback segments, and then when it runs out of rollback segments, it fails.
How can your query acquire other RBS when u've assigned one RBS particularly for your query ? Can u post the error ?
However, have u tried to do that using a PL-SQL or a part update ?
Vinit
-
-
Take the ROLLBACK SEGMENT immeditaly offline after the SET TRANSACTION command.
This puts the Rollback seg into a OFFLINE PENDING state while the transaction is running. It means no other transactions can utilise the segment at the same time as the running trasaction.
Cheers,
OCP 8i, 9i DBA
Brisbane Australia
-
no version mentioned....in 9i transactions span rbs segments and *borrow* space from another rbs if needed
I'm stmontgo and I approve of this message
-
Originally posted by grjohnson
Take the ROLLBACK SEGMENT immeditaly offline after the SET TRANSACTION command.
This puts the Rollback seg into a OFFLINE PENDING state while the transaction is running. It means no other transactions can utilise the segment at the same time as the running trasaction.
Cheers,
But your solution will work after he is able to hook the big_rbs. It seems he is unable to get that big RBS.
Sanjay
-
Another solution will be to do a commit per million of rows, whatever.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|