CREATE OR REPLACE PACKAGE statspack_admin
AS
/******************************************************************************
   NAME:       statspack_admin
   PURPOSE:    Administer statspack - This package will help make statspack
                                      administration automatic and easy.
                                      ©2004 - Edward Stoever
                                      
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/9/2004    Edward Stoever   1. Created this package.
******************************************************************************/
   PROCEDURE purge_old_snaps (days_ago NUMBER DEFAULT 10);

   PROCEDURE purge_once_per_day (days_ago NUMBER DEFAULT 10);

   PROCEDURE resubmit_snap_jobs (snaps_per_day NUMBER DEFAULT 12);
END statspack_admin;
/
CREATE OR REPLACE PACKAGE BODY statspack_admin
AS
/******************************************************************************
   NAME:       statspack_admin
   PURPOSE:    Administer statspack - This package will help make statspack
                                      administration automatic and easy.
                                      ©2004 - Edward Stoever
                                      
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/9/2004    Edward Stoever   1. Created this package body.
******************************************************************************/
   FUNCTION instance_number
      RETURN NUMBER
   IS
      inst   NUMBER;
   BEGIN
      SELECT instance_number
        INTO inst
        FROM v$instance;

      RETURN inst;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 1;
   END;

   FUNCTION today_at_1am
      RETURN DATE
   IS
      var_today_at_1am   DATE;
   BEGIN
      var_today_at_1am :=
         TO_DATE (TO_CHAR (SYSDATE, 'MM/DD/YYYY') || ' 1:00:00 AM',
                  'MM/DD/YYYY HH:MI:SS AM'
                 );
      RETURN var_today_at_1am;
   END;

   FUNCTION convert_days_ago (days_ago NUMBER)
      RETURN NUMBER
   IS
      xdays   NUMBER;
   BEGIN
      IF days_ago > 30
      THEN
         xdays := 30;
      ELSE
         xdays := FLOOR (ABS (days_ago));
      END IF;

      RETURN xdays;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 30;
   END;

   PROCEDURE purge_old_snaps (days_ago NUMBER DEFAULT 10)
   /*
   THIS PROCEDURE WAS DERIVED FROM $ORACLE_HOME/RDBMS/ADMIN/SPPURGE.SQL
   AND IS USED TO DELETE OLD SNAPS.
   IT CAN BE SUBMITTED AS A JOB VIA DBMS_JOB.
   */
   IS
      dbid          NUMBER;
      inst_num      NUMBER;
      inst_name     VARCHAR2 (20);
      db_name       VARCHAR2 (20);
      max_snap_id   NUMBER         := 0;
      btime         VARCHAR2 (100);
      etime         VARCHAR2 (100);
      xdays         NUMBER;
   BEGIN
      xdays := convert_days_ago (days_ago);

      SELECT d.dbid dbid, d.NAME db_name, i.instance_number inst_num,
             i.instance_name inst_name
        INTO dbid, db_name, inst_num,
             inst_name
        FROM v$database d, v$instance i
       WHERE ROWNUM = 1;

      --delete everything from xdays days ago!
      SELECT MAX (s.snap_id)
        INTO max_snap_id
        FROM stats$snapshot s
       WHERE s.dbid = dbid
         AND s.instance_number = inst_num
         AND s.snap_time < (SYSDATE - xdays);

      IF max_snap_id <> 0
      THEN
         SELECT TO_CHAR (snap_time, 'YYYYMMDD HH24:MI:SS')
           INTO btime
           FROM stats$snapshot b
          WHERE b.snap_id =
                       (SELECT MIN (s.snap_id)
                          FROM stats$snapshot s
                         WHERE s.dbid = dbid AND s.instance_number = inst_num)
            AND b.dbid = dbid
            AND b.instance_number = inst_num;

         SELECT TO_CHAR (snap_time, 'YYYYMMDD HH24:MI:SS')
           INTO etime
           FROM stats$snapshot e
          WHERE e.snap_id = max_snap_id
            AND e.dbid = dbid
            AND e.instance_number = inst_num;

         DELETE FROM stats$snapshot
               WHERE instance_number = inst_num
                 AND dbid = dbid
                 AND snap_id <= max_snap_id;

/*  Delete any undostat rows that cover the snap times  */
         DELETE FROM stats$undostat us
               WHERE dbid = dbid
                 AND instance_number = inst_num
                 AND begin_time < TO_DATE (btime, 'YYYYMMDD HH24:MI:SS')
                 AND end_time > TO_DATE (etime, 'YYYYMMDD HH24:MI:SS');

/*  Delete any dangling database instance rows for that startup time  */
         DELETE FROM stats$database_instance di
               WHERE instance_number = inst_num
                 AND dbid = dbid
                 AND NOT EXISTS (
                        SELECT 1
                          FROM stats$snapshot s
                         WHERE s.dbid = di.dbid
                           AND s.instance_number = di.instance_number
                           AND s.startup_time = di.startup_time);

/*  Delete any dangling statspack parameter rows for the database instance  */
         DELETE FROM stats$statspack_parameter sp
               WHERE instance_number = inst_num
                 AND dbid = dbid
                 AND NOT EXISTS (
                        SELECT 1
                          FROM stats$snapshot s
                         WHERE s.dbid = sp.dbid
                           AND s.instance_number = sp.instance_number);

         COMMIT;
      END IF;
	  
	   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
   END purge_old_snaps;

   PROCEDURE purge_once_per_day (days_ago NUMBER DEFAULT 10)
   /* THIS WILL PURGE OLD SNAPS ONCE PER DAY AT 1:00 AM */
   IS
      CURSOR c_1
      IS
         SELECT job
           FROM user_jobs
          WHERE LOWER (TRIM (what)) LIKE '%purge_old_snaps%';

      var_purge_job   NUMBER;
      instno          NUMBER;
      jobno           NUMBER;
      x_days          NUMBER;
   BEGIN
      x_days := convert_days_ago (days_ago);

      OPEN c_1;

      LOOP
         FETCH c_1
          INTO var_purge_job;

         EXIT WHEN c_1%NOTFOUND;
         DBMS_JOB.remove (var_purge_job);
      END LOOP;

      CLOSE c_1;

      instno := instance_number;
      DBMS_JOB.submit (jobno,
                          'statspack_admin.purge_old_snaps('
                       || TO_CHAR (x_days)
                       || ');',
                       TRUNC (today_at_1am + 1, 'HH'),
                       'trunc(SYSDATE+1,''HH'')',
                       TRUE,
                       instno
                      );
      COMMIT;
   END purge_once_per_day;

   PROCEDURE resubmit_snap_jobs (snaps_per_day NUMBER DEFAULT 12)
   /*  Possible values for snaps_per_day: 24, 12, 6, 4  */
   IS
      CURSOR c_1
      IS
         SELECT job
           FROM user_jobs
          WHERE LOWER (TRIM (what)) LIKE '%statspack.snap%';

      var_snap_job        NUMBER;
      instno              NUMBER;
      jobno               NUMBER;
      var_snaps_per_day   NUMBER;
   BEGIN
      IF snaps_per_day > 12
      THEN
         var_snaps_per_day := 24;
      ELSIF snaps_per_day <= 12 AND snaps_per_day > 6
      THEN
         var_snaps_per_day := 12;
      ELSIF snaps_per_day <= 6 AND snaps_per_day > 4
      THEN
         var_snaps_per_day := 6;
      ELSE
         var_snaps_per_day := 4;
      END IF;

      OPEN c_1;

      LOOP
         FETCH c_1
          INTO var_snap_job;

         EXIT WHEN c_1%NOTFOUND;
         DBMS_JOB.remove (var_snap_job);
      END LOOP;

      CLOSE c_1;

      instno := instance_number;
      DBMS_JOB.submit (jobno,
                       'statspack.snap;',
                       TRUNC (SYSDATE + 1 / var_snaps_per_day, 'HH'),
                          'trunc(SYSDATE+1/'
                       || TO_CHAR (var_snaps_per_day)
                       || ',''HH'')',
                       TRUE,
                       instno
                      );
      COMMIT;
   END resubmit_snap_jobs;
END statspack_admin;
/