Statspack Purge.

Hi guys
I am working on a project that will need STATSPACK.
I have installed it and also run the spauto.sql to gather hourly snapshot on the database.
Our goal here is to keep 12 months of hourly snapshot (that 8064 snaps)and purge anything beyond 12 months and I was asked not to use a cron, maestro or any other scheduler to do the purge but to use something similar the Oracle "spauto" that get hourly snap everyday.

Can sppurge.sql be manipulated and run as dbms_job and act the same way as spauto that you run once and get the snapshot every hour for you and ONLY purge if we have beyond 12 months and if we do not have 12 months of
snap retrun "no rows purged" or "No snapshot purge is necessary"

Any idea
Thanks

What version of Oracle? If 10g, Have a look at Automatic Workload Repository? Let MMON do the task of collecting and purging snapshots.

If earlier than 10g, You have to manually delete statspack report using dbms_job. Make a copy of SPPURGE.sql and then manipulate to your need.

Well use the fact that "STATSPACK uses foreign-key referential integrity constraints with the ON CASCADE DELETE option".

http://www.dba-oracle.com/tips_oracl...ge_utility.htm

Code:

---

SQL> select SNAP_ID,to_char(SNAP_TIME,'dd-mon-yyyy hh24:mi') from stats$snapshot;

  SNAP_ID TO_CHAR(SNAP_TIME
---------- -----------------
        1 07-mar-2007 11:35
        2 07-mar-2007 11:36
        3 07-mar-2007 11:36

3 rows selected.

SQL> delete from stats$snapshot where SNAP_TIME < sysdate - (1/(24*60)) ;

3 rows deleted.

---

Extend this to 12 months and test it. Good luck.

use sppurge

Thanks for all the response.

1) I have suggested the Automatic Workload Repository for few 10g databases we have but upper management would like consistentcy accross the bord.(STATSPACK all the way) so this is not an option.

2) sppurge is manual as you need to supply losnapid and hisnapid. They something that you run once and will call the dbms_job to purge on its on every day provided that the number of day or snapshots to keep is repected( ie we only need 12 months worth of snapshot..anything old beyond 12 months that will be automatically purged.
Thanks

configure it then to accept inputs

Dude , check this out:
http://www.idevelopment.info/data/Or...tom_pkg_9i.sql

Please test it before you use it.