Re: [Q] Rollback segment deallocation

Noah Monsey (noah_at_indirect.com) wrote:
: mlcampb1_at_ingr.com (Mike Campbell) wrote:
 

: >I've been trying to do some testing to make my rollback segments shrink
: >back to the optimal size I've defined, but can't seem to make them decrease
: >in size. If I create a rollback segment with an initial size of 20K and set and
: >optimal size at 26K I can use sqldba monitor rollback segments to verify
: >that it got created successfully.
 

: >Now I use the set transaction command to force Oracle to use my newly
: >created rollback segment and insert 7500 rows into a table. In another
: >window I can see the size of my rollback segment grow continually as well
: >as additional extents being allocated.
 

: >Once I commit the inserts though I thought that my rollback segment would
: >reduce back to my optimal 26K size but it doesn't. I even tried issuing
: >another command to force it to extend my rollback segment further, but
: >once again when it is finished and I commit, the space is still allocated to
: >the rollback segment.
 

: >What gives here? Am I doing something wrong or what? How do I force the
: >rollback segments to shrink?
 

: The only way ( that I know of ) to get the rollback segment
: back to a particular size is to drop the segment and
: recreate it. If you are planning to run a large
: transaction against the database, you will end up
: with some large rollback segments. Another
: good idea is to plan ahead and create a few
: large rollback segments before you start the
: large transaction.
 

: A good strategy for dealing with rollback segments,
: is to put them in their own tablespace. The same thing
: applies to the user data and the temporary data.
: That way as your large transaction is running, the
: tablespace with your data does not get fragmented,
:
:
: ========= ========= ========= ========= =========
: Noah Monsey noah_at_indirect.com
: ========= ========= ========= ========= =========
: The only dumb question is the one that you don't ask.
: ========= ========= ========= ========= =========
: Oracle Master Database Administration April 1992
: Oracle Master Application Development April 1992
: ========= ========= ========= ========= =========

We are using rollback segments with an optimal size and they do shrink back. This does not occur immediately, and I have not actually monitored them to find out exactly when it occurs. Wait a while and examine the rollback statistics in monitor. You will see that the rollback segments have shrunk back.

Sandra Rennick
Contractor Received on Wed May 31 1995 - 00:00:00 CEST