Re: PERFSTAT tablespace and sppurge.sql

On Dec 10, 7:54 am, trub3101 <trub3..._at_sky.com> wrote:
> On 10 Dec, 12:44, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On 10 dec, 12:31, trub3101 <trub3..._at_sky.com> wrote:
>
> > > Hi all,
>
> > > Yesterday I purged 80% of the my statspack snapshots using sppurge.sql
> > > however, the PERFSTAT tablespace remains the same size as before the
> > > purge.
>
> > > My question is (and somehow I believe I should know the answer to
> > > this!) how comes the PERFSTAT tablespace has not shrunk accordingly?
>
> > > Thanks in advance,
>
> > > tb3101
>
> > Because tablespaces behave like humans ;)
> > They gain weight easily, but they only loose it with hard work and
> > exercise...
>
> > Seriously: Oracle has never deallocated space automatically. This is
> > obvious as both allocations and de-allocations are costly operations.
> > Oracle doesn't have the 'jo-jo effect' observed in persons who try to
> > slim down.
> > You would need to resize the tablespace yourself. Preferably before
> > Christmas ;)
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Many thanks Sybrand!
>
> That pretty much explains it!- Hide quoted text -
>
> - Show quoted text -

I do like Sybrand's anology. I will offer that if you feel it is necessary to trim space from a tablespace for reasons other than some special event such as the permanent removal of a large object from the tablespace that you probably should not have allowed the tablespace to grow in the first place. Trimming file sizes should not be a regular function of the DBA. Normally space freed within a tablespace should remain assigned to the tablespace for reuse.

I can understand wanting to clean up after Perfstat if the statspack information has been allowed to accumulate for a long period of time. If you do not run statspack that often and you really want to maximize the file space freed you should consider reorganizing the Perfstat objects.

HTH -- Mark D Powell -- Received on Wed Dec 10 2008 - 08:08:57 CST