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

Some ideas...

1. use the sppurge.sql script, but check to see if some of the statements are commented out (due to their expense). You might want to schedule those ones in during a quiet time.
2. 27G seems a lot - check your disk/buffer thresholds for the statspack parameters - maybe you are collecting data on things you don't really need to.
3. if you've got lots of free space that is not being reused, you could use 'alter table move' on each statspack table (and rebuilding the indexes) to reclaim that space.

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------