Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to decrease size of Rollback Segments Tablespace
On Fri, 28 May 2004 15:51:50 +0200, "André Hartmann"
<andrehartmann_at_hotmail.com> 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é
>:)
>
alter the rollback segment to offline
drop them
resize the tablespace to something sensible
set autoextend on the tablespace off.
Create the rollback segments with proper parameters, making sure they
will rather hit maxextents on the rollback segment, instead of
consuming all available space.
Also, rman supports incremental backups.
-- Sybrand Bakker, Senior Oracle DBAReceived on Fri May 28 2004 - 12:32:10 CDT