Re: How to decrease size of Rollback Segments Tablespace

André Hartmann wrote:

> Hi there,
>
> i have the following problem: On my Oracle Server (Win2000, 9i, Archived
> Log Mode, Rollback Segments) my rollback segment tablespace has grown 10GB
> big and I want to make it smaller. The tablespace contains just 1 data file
> that is 10GB big.
>
> Normally with a standard data file I use OEM to "reorganize" the
> tablespace, like a defragmentation, and then decrease the size of it.
> However with the rollback tablespace OEM tells me reorganization is not
> (yet) supported. And I am unable to just decrease the size of the file
> because I get the all famous "ORA-03297 file contains used data beyond
> requested RESIZE value" message. However, only 4% of the file are actually
> used so there is the potential to decrease it quite a bit, its just that the
> data blocks occupied in the file lie more towards the end of the file.
>
> Any idea what I can do ? I already decreased the sizes of the rollback
> segments, but that didnt release enough blocks in the data file. So I seem
> stuck. Reducing the filesize would also reduce the size of my physical full
> backups, so it would be a double gain... I am hoping for applicable
> suggestions on here,
>
> thanks,
>
> André
> :)

The simplest solution. And the one that will likely improve overall performance and scalability is to drop all rollback segments. Drop the tablespace. And build an undo tablespace.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)