Re: [Q] Rollback segment deallocation

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
• ========= ========= ========= =========