Rollback Segments growing... 911!!!

[THoey]

I originally posted this in the Oracle 5/6/7 group, but thought I might have a better chance at a response if I put it here also...

*******************

I have a table that gets loaded with approximately 300K records each day. Normally I have a script that deletes records that are more than two weeks old, but it was disabled last month to collect some extra data.

Now, I am going through and cleaning up that table, putting it back to the two week limit. Since there are so many records to delete, I am doing it one day at a time, so that I don't over load the rollback segments. I delete a days worth of records and then do a commit. I thought this would free up space in the rollback segments, but using Oracle Storage Manager, I see that the size just keeps growing. The size of the available rollback segment is 1 gig and it is currently filled to 670 meg. I can increase the available size, but this doesn't seem right.

Is there a setting I need to use to free up this space upon a commit or am I just wrong in my understanding of this?

Any help soon would be greatly appreciated as I have another 8 days worth of data to delete.

Thanks in advance...



Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[mark06]

Don't know what version of Oracle you're using and I'm by no means a Rollback Segment 'guru', but you could try setting the OPTIMAL parameter which causes automatic shrinking (when I don't know!), or you could use 'alter rollback segment <rbs> shrink to 10M (for example)'.

A useful view is v$rollstat for rollback segment activity.

Good luck - I hate these blighters.

Cheers,
Mark.

 

[MikeLacey]

there's an option to shrink rbs's in storage manager isn't there?

Mike
michael.j.lacey@ntlworld.com

http://www.cargill.com/

 

[THoey]

I think I finally ended up solving this by restarting Oracle. Went from 800M to 400M. Of course this is just a temp fix. I will look into &quot;shrinking&quot; on Monday...

Thanks Mike...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?