Re: How to decrease size of Rollback Segments Tablespace

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:40b743f6$1_at_olaf.komtel.net...
> 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é
> :)

You could move to using automatic undo, and then the problem goes away, for ever.

You could also stop using autoextend for the rollback tablespace, and then the problem would never have arisen in the first place.

Re-organising rollback tablespace essentially requires dropping unneeded rollback segments. And rollback segments need to be brought offline before they can be dropped.

But really: use automatic undo.

Regards
HJR Received on Fri May 28 2004 - 13:53:20 CDT