This is quite simply the Oracle supplied sppurge.sql script turned into a package, with a couple of convience procedures exposed.
I wrote this package a while back to automate the purging of the statspack schema tables using DBMS_JOB. Scripts are not easy to schedule, especially if you have to support multiple operating systems with differing scheduling capabilities, whereas the database has a built in scheduling interface in the form of the Job Queue; simple, powerful and most importantly consistent across all platforms.
One of the procedures simply deletes all snaps between 2 dates (unlike the original script that demanded snap IDs), the other procedure will move all of the statspack objects into a different tablespace during a given time window. This last procedure was written, not for misguided reasons to do with performance, but because once upon a time due to time constraints, I created the PERFSTAT schema in the SYSTEM tablespace...
Unlike all my other PL/SQL creations, this code is NOT wrapped because it's not really mine. Feel free to change it, but you're on your own if you do.
This package has been extensively tested and deployed on several production systems running 9i.
This package has not been tested on 8i, but it should work on versions 8.1.6 and 8.1.7.
This package will NOT work on versions 8.1.5 and before.
This package is not required in 10g as Oracle vastly improved the statspack
package and provided a purge procedure. |
|
Download
spackadm.sql
File Information
Name - spackadm.sql
Size - 8KB
Version - 1.0
Date - 25 Apr 2006
Download
spackadm.sql |
|
|