Re: De-framentation of segments

From: Rachel Carmichael <rachel.carmichael_at_citicorp.com> >> I got the following error when trying to insert about 60K rec into a table: ORA-01562: failed to extend rollback segment number 4 ORA-01628: max # extents (121) reached for rollback segment RB2. I increased the number of max number of segments and that go rid of the problem. My question is could I have gotten around the error by de-fragmenting the segment? If so how do I find out if the data is fragmented and how do I de-fragment it? <<
With version 7.3+ there is an option to the 'alter rollback segment segname' command: shrink. There are a couple of optional parameters that you can also use to control the number and size of extents released.

You can still drop and re-create the segment as with versions 7.2 and below.

I suggest you read v$rollstat for the number of wraps and shrinks that your segments have been taking. You may need to rebuild your segments with larger initial and next extent sizes. I would recommend that the initial and next extent match, and that all segments be allocated multiple extents. Set the optimal value to the number of extents the extent size.

This way the number of extents X the extent size X the number of rollback segments can be set to amount of space you always want available to hold change data, and the remaining rollback tablespace data will be available for the large jobs.

Just a thought. It works for us as we have several user launced day-time interactive batch jobs that update 100M while a couple hundred users do small transactions. Working out the right number and size of rollback segments is almost an art.

Good Luck.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Tue Mar 24 1998 - 00:00:00 CST