Re: Rollback Segment Question

Since you don't state a version of oracle I'll assume that you mean 8.1.7.x.4.
(hint: list version of oracle in your postings, OS info is not a bad
thing to post either).

One possible explanation is that you do not have optimal set for any of the rollback segments and that the number of rollback segments defined * largest transaction > 4 GB. (fewer rollback segments might help).

Another possible explanation is that the number of simultaneous transactions / number of rollback segments >> 1 and more than one
(possibly several, many or all) of the rollback segments have grown to
some multiple of your largest transaction.

How did you arrive at the number of rollback segments? Perhaps fewer, larger rollback segments would be better? How long running are the long transactions, what size are they?

Short term ... Add an additional tablespace with large rollback segments and use "SET TRANSACTION USE ROLLBACK SEGMENT <segment_name>;" ... if you can touch the app code. The downside to this is that you cannot use that against SMU in 9.2 (you can and set an event to ignore the error message).

Perhaps you should just upgrade to 9.2, test with system managed undo and continue the battle from there? You will still likely require more than 4 GB of space depending upon your undo_retention_time value.

hth.

-bdbafh Received on Mon Apr 25 2005 - 09:23:03 CDT