-
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
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
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.
-- Dilip
-
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.
-
-
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
Last edited by Ablakios; 03-07-2007 at 09:49 AM.
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
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.
Milind
-----------------------------
www.milinds.com
My Private World on the Web !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|