Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to decrease size of Rollback Segments Tablespace

Re: How to decrease size of Rollback Segments Tablespace

From: Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl>
Date: Fri, 28 May 2004 19:32:10 +0200
Message-ID: <joteb01ra0qkkpt8nnv3585ui737ka7aqm@4ax.com>


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 DBA
Received on Fri May 28 2004 - 12:32:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US