The original question on this thread was for an automated "purge" for STATSPACK. I wrote this stored procedure based on the v8.1.7 version of the standard "sppurge.sql" script. I'd use that script, except I don't like the way it is called (i.e. range of SNAP_IDs). This stored procedure figures out the range of SNAP_IDs based on the parameter indicating the number of days of data to retain...
Hope this helps -- as always, no warranties! ----------------------- begin included SQL*Plus script ------------------------------ /********************************************************************** * File: sppurpkg.sql * Type: SQL*Plus script * Author: Tim Gorman (Evergreen Database Technologies, Inc.) * Date: 18Oct01 * * Description: * SQL*Plus script containing DDL commands to create the package * SPPURPKG, intended for use with STATSPACK from Oracle database * versions 8.1.7 and above. Adapted from the "sppurge.sql" script * which is included with standard STATSPACK v8.1.7, it is easier * to use because it can be called automatedly from the DBMS_JOB * package (instead of interactively as with "sppurge.sql") and it * takes only the number of days of STATSPACK data to retain * (instead of prompting for a begin/end range of SNAP_IDs, like * "sppurge.sql") * * After the package is created, then this script will submit the * procedure "SPPURPKG.RUN(14)" (i.e. purge data older than 14 * days) to run once per day. You may want to modify this, * depending on the volume of activity on the database(s) being * monitored by STATSPACK and the amount of storage you are * prepared to allocate to the PERFSTAT schema... * * Modifications: *********************************************************************/ set echo on feedback on timing on verify on spool sppurpkg connect perfstat show user show release set termout off create or replace package SPPURPKG is -- procedure PURGE(in_days_older_than IN INTEGER); -- end SPPURPKG; / set termout on show errors set termout off create or replace package body SPPURPKG is -- procedure PURGE(in_days_older_than IN INTEGER) is -- cursor get_snaps(in_days IN INTEGER) is select s.rowid, s.snap_id, s.dbid, s.instance_number from stats$snapshot s, sys.v_$database d, sys.v_$instance i where s.dbid = d.dbid and s.instance_number = i.instance_number and s.snap_time < trunc(sysdate) - in_days; -- errcontext VARCHAR2(100); errmsg VARCHAR2(1000); save_module VARCHAR2(48); save_action VARCHAR2(32); -- begin -- errcontext := 'save settings of DBMS_APPLICATION_INFO'; dbms_application_info.read_module(save_module, save_action); dbms_application_info.set_module('SPPURPKG.PURGE', 'begin'); -- errcontext := 'open/fetch get_snaps'; dbms_application_info.set_action(errcontext); for x in get_snaps(in_days_older_than) loop -- errcontext := 'delete (cascade) STATS$SNAPSHOT'; dbms_application_info.set_action(errcontext); delete from stats$snapshot where rowid = x.rowid; -- errcontext := 'delete "dangling" STATS$SQLTEXT rows'; dbms_application_info.set_action(errcontext); delete from stats$sqltext where (hash_value, text_subset) not in (select /*+ hash_aj(ss) */ hash_value, text_subset from stats$sql_summary ss ); -- errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows'; dbms_application_info.set_action(errcontext); delete from stats$database_instance i where i.instance_number = x.instance_number and i.dbid = x.dbid and not exists (select 1 from stats$snapshot s where s.dbid = i.dbid and s.instance_number = i.instance_number and s.startup_time = i.startup_time ); -- errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows'; dbms_application_info.set_action(errcontext); delete from stats$statspack_parameter p where p.instance_number = x.instance_number and p.dbid = x.dbid and not exists (select 1 from stats$snapshot s where s.dbid = p.dbid and s.instance_number = p.instance_number ); -- errcontext := 'fetch/close get_snaps'; dbms_application_info.set_action(errcontext); -- end loop; -- errcontext := 'restore saved settings of DBMS_APPLICATION_INFO'; dbms_application_info.set_module(save_module, save_action); -- exception -- when OTHERS then errmsg := sqlerrm; dbms_application_info.set_module(save_module, save_action); raise_application_error(-20000, errcontext || ': ' || errmsg); -- end PURGE; -- end SPPURPKG; / set termout on show errors variable jobno number; begin dbms_job.submit(:jobno, 'sppurpkg.purge(14);', sysdate+(1/1440), 'SYSDATE+1', TRUE); commit; end; / set pages 100 select * from user_jobs where job = :jobno; spool off ----------------------- end included SQL*Plus script ------------------------------ ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, August 07, 2002 4:16 PM > > Hey Jared, > > Did you do anything with statspack or the wait interface in your > > toolset/book? Gotta get a copy for inspiration. > > No, no wait interface stuff, at least, I don't remember any. > > You're correct about writing code for others to see. It's one > thing to write code for internal use, but just try packaging > it and writing coherent documentation. The amount of effort > shoots *wayyyy* up. > > I have ideas for new iterations of the toolkit that is > included in the book, but I'll wait and see what others > like and dislike, and what they think is missing. > > Probably a lot. Can't really pack too much into a couple > hundred pages. > > Jared > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).