Re: How to delete STATSPACK data efficient to release back space?

dominica_at_gmail.com wrote:
> Hi All,
>
> I am running oracle 8.1.7.4 (32 bit) on 64 bit solaris 2.9 platform.
>
> I am running statpack every 15 minutes.( to collect information like
> execute statspack.snap)
>
> I have a question, what is the best way to maintain the size of the
> tablespace used
> by STATPACK?
>
> I notice now it is using like 27G now.
>
> I want to delete a bunch of data and resize the tablespace back.
> But in my past experience with this same database, I notice, even if I
> delete like 1 months's data, look like the new data is not going in the
> "delete space" and my tablespace's size keep growing..
>
> Any good idea??
>
> Thanks,
>
>
> Dominica

Statspack comes with a script called sppurge. Based on that script you could easily set up a pl/sql job to delete everything older than 3 months.
I believe all statspack related tables are created with the default pctfree and pctused values. In this particular case that might be a bad idea. I never changed them though, but I just either issue alter table move commands, or have Toad rebuild them. Running statspack every 15 minutes, in my experience, doesn't provide any useful information, and -especially when run on level 5 or higherhas  a negative impact on the performance of the database in question. You may want to decrease the interval significantly, and run manual statspack.snap when users start complaining.

-- 
Sybrand Bakker
Senior Oracle DBA