-- +----------------------------------------------------------------------------+
-- |                          Jeffrey M. Hunter                                 |
-- |                      jhunter@idevelopment.info                             |
-- |                         www.idevelopment.info                              |
-- |----------------------------------------------------------------------------|
-- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : sp_purge_30_days_9i.sql                                         |
-- | CLASS    : Statspack                                                       |
-- | PURPOSE  : This script is responsible for removing all Statspack snapshot  |
-- |            records older than 30 days. Most of the code contained in this  |
-- |            script is modeled after the Oracle supplied sppurge.sql script  |
-- |            but removes by Snapshot date rather than Snapshot IDs.          |
-- |                                                                            |
-- |            Note that this script only works with Oracle9i.                 |
-- |                                                                            |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+

SET LINESIZE  145
SET PAGESIZE  9999
SET FEEDBACK  off
SET VERIFY    off

DEFINE days_to_keep=30

UNDEFINE dbid inst_num hisnapid

WHENEVER SQLERROR EXIT ROLLBACK

SPOOL sp_purge_&days_to_keep._days_9i.lis


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Get database and instance currently connected to. This will be used later  |
prompt | in the report along with other metadata to lookup snapshots.               |
prompt +----------------------------------------------------------------------------+

SET FEEDBACK off

COLUMN inst_num   FORMAT 99999999999999  HEADING "Instance Num."  NEW_VALUE inst_num
COLUMN inst_name  FORMAT a15             HEADING "Instance Name"  NEW_VALUE inst_name
COLUMN db_name    FORMAT a10             HEADING "DB Name"        NEW_VALUE db_name
COLUMN dbid       FORMAT 9999999999      HEADING "DB Id"          NEW_VALUE dbid

SELECT
    d.dbid             dbid
  , d.name             db_name
  , i.instance_number  inst_num
  , i.instance_name    inst_name
FROM
    v$database d
  , v$instance i
/


VARIABLE dbid        NUMBER;
VARIABLE inst_num    NUMBER;
VARIABLE inst_name   VARCHAR2(20);
VARIABLE db_name     VARCHAR2(20);

BEGIN
  :dbid      :=  &dbid;
  :inst_num  :=  &inst_num;
  :inst_name := '&inst_name';
  :db_name   := '&db_name';
END;
/

SET FEEDBACK on


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Obtain the MIN and MAX Snapshot IDs to be removed from the range of IDs    |
prompt | order than &days_to_keep days.                                                        |
prompt +----------------------------------------------------------------------------+

SET FEEDBACK off

COLUMN lo_snap    HEADING "Min Snapshot ID"  NEW_VALUE LoSnapId
COLUMN hi_snap    HEADING "Max Snapshot ID"  NEW_VALUE HiSnapId

SELECT  NVL(MAX(snap_id),0)    hi_snap
      , NVL(MIN(snap_id),0)    lo_snap
FROM    stats$snapshot
WHERE   snap_time < (sysdate - &days_to_keep);

VARIABLE lo_snap   NUMBER;
VARIABLE hi_snap   NUMBER;

BEGIN 
  :lo_snap   :=  &losnapid;
  :hi_snap   :=  &hisnapid; 
END;
/


COLUMN l  HEADING 'Low Snap ID'
COLUMN h  HEADING 'High Snap ID'

SELECT
    :lo_snap  l
  , :hi_snap  h
FROM dual;

SET FEEDBACK ON


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Snapshots that will be removed for this database instance.                 |
prompt +----------------------------------------------------------------------------+

SET FEEDBACK off

COLUMN snap_id    FORMAT 9999990  HEADING 'Snap Id'
COLUMN level      FORMAT 99       HEADING 'Snap|Level'
COLUMN snap_date  FORMAT a21      HEADING 'Snapshot Started'
COLUMN host_name  FORMAT a15      HEADING 'Host'
COLUMN ucomment   format a25      HEADING 'Comment' 

SELECT
    s.snap_id                                      snap_id
  , s.snap_level                                   "level"
  , to_char(s.snap_time,'mm/dd/yyyy HH24:MI:SS')   snap_date
  , di.host_name                                   host_name
  , s.ucomment                                     ucomment
FROM
    stats$snapshot           s
  , stats$database_instance  di
WHERE
      s.dbid              = :dbid
  AND di.dbid             = :dbid
  AND s.instance_number   = :inst_num
  AND di.instance_number  = :inst_num
  AND di.startup_time     = s.startup_time
  AND s.snap_id           < :hi_snap
ORDER BY
    db_name
  , instance_name
  , snap_id
/

SET HEADING off

SELECT 'WARNING: No snapshots found older than &days_to_keep days in STATS$SNAPSHOT'
FROM   dual
WHERE  NOT EXISTS 
       (
         select null
         from   stats$snapshot
         where  instance_number = :inst_num
           and  dbid            = :dbid
           and  snap_id         = :lo_snap
       )
       OR
       NOT EXISTS
       (
         select null
         from   stats$snapshot
         where  instance_number = :inst_num
           and  dbid            = :dbid
          and  snap_id         = :hi_snap
       )
/

SET HEADING on
SET FEEDBACK ON



prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Get begin and end snapshot times - these are used to delete undostat.      |
prompt +----------------------------------------------------------------------------+

SET FEEDBACK OFF

COLUMN btime  NEW_VALUE btime
COLUMN etime  NEW_VALUE etime

SELECT
    b.snap_id
  , TO_CHAR(b.snap_time, 'YYYYMMDD HH24:MI:SS') btime
FROM
    stats$snapshot b
WHERE
      b.snap_id         = :lo_snap
  AND b.dbid            = :dbid
  AND b.instance_number = :inst_num;


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

VARIABLE btime   VARCHAR2(25);
VARIABLE etime   VARCHAR2(25);

BEGIN 
  :btime     :=  '&btime';
  :etime     :=  '&etime';
END;
/

SET FEEDBACK on


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Deleting snapshots older than &days_to_keep days.
prompt | Deleting snapshots &&losnapid - &&hisnapid.
prompt +----------------------------------------------------------------------------+

DELETE FROM stats$snapshot
 WHERE instance_number = :inst_num
   AND dbid            = :dbid
   AND snap_id between :lo_snap and :hi_snap;


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Delete any dangling SQLtext. The following statement deletes any dangling  |
prompt | SQL statements which are no longer referred to by ANY snapshots. By        |
prompt | default, Oracle comments this statement out as it can be very resource     |
prompt | intensive.                                                                 |
prompt +----------------------------------------------------------------------------+

SET FEEDBACK off

ALTER SESSION SET hash_area_size=1048576;

COLUMN last_snap_id  HEADING 'Last Snap ID'
COLUMN count         HEADING 'Count'

SELECT --+ index_ffs(st)
    last_snap_id
  , count(*)      count
FROM
    stats$sqltext st
WHERE
    (   hash_value
      , text_subset
    )
    NOT IN
    (  select --+ hash_aj full(ss) no_expand 
           hash_value
         , text_subset
       from stats$sql_summary ss
       where ( (
                 snap_id     < :lo_snap
                 or
                 snap_id     > :hi_snap
               )
               and dbid            = :dbid
               and instance_number = :inst_num
             )
             or
             (
               dbid            != :dbid
               or
               instance_number != :inst_num
             )
    )
GROUP BY
    last_snap_id;

SET FEEDBACK on

DELETE --+ index_ffs(st) 
FROM  stats$sqltext st
WHERE
    (   hash_value
      , text_subset
    )
    NOT IN
    (  select --+ hash_aj full(ss) no_expand 
           hash_value
         , text_subset
       from stats$sql_summary ss
       where ( (
                 snap_id     < :lo_snap
                 or
                 snap_id     > :hi_snap
               )
               and dbid            = :dbid
               and instance_number = :inst_num
             )
             or
             (
               dbid            != :dbid
               or
               instance_number != :inst_num
             )
    );


SET FEEDBACK on

prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | (OPTIONAL) - STATS$SEG_STAT_OBJ delete statement                           |
prompt +----------------------------------------------------------------------------+

DELETE --+ index_ffs(sso)
FROM  stats$seg_stat_obj sso
WHERE (   dbid
        , dataobj#
        , obj#
      )
      NOT IN
      (
        select --+ hash_aj full(ss) no_expand
            dbid
          , dataobj#
          , obj#
        from
            stats$seg_stat ss
        where ( ( snap_id     < :lo_snap
                  or
                  snap_id     > :hi_snap
                 )
                 and dbid            = :dbid
                 and instance_number = :inst_num
              )
              or
              ( dbid            != :dbid
                or
                instance_number != :inst_num
              )
      );


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Delete from stats$undostat                                                 |
prompt | Undostat rows that cover the snap times                                    |
prompt +----------------------------------------------------------------------------+

COLUMN dbid             HEADING 'DB Id'
COLUMN instance_number  HEADING 'Instance Number'
COLUMN snap_id          HEADING 'Snap ID'
COLUMN begin_time       HEADING 'Begin Time'
COLUMN end_time         HEADING 'End Time'

SELECT
    dbid
  , instance_number
  , snap_id
  , TO_CHAR(begin_time, 'YYYYMMDD HH24:MI:SS') begin_time
  , TO_CHAR(end_time,   'YYYYMMDD HH24:MI:SS') end_time
FROM
    stats$undostat us
WHERE
      dbid            = :dbid
  AND instance_number = :inst_num
  AND end_time        <  to_date(:etime, 'YYYYMMDD HH24:MI:SS')
ORDER BY
    snap_id;


DELETE from stats$undostat us
 WHERE dbid            = :dbid
   AND instance_number = :inst_num
   AND end_time        <  to_date(:etime, 'YYYYMMDD HH24:MI:SS');


prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Delete from stats$database_instance                                        |
prompt | Dangling database instance rows for that startup time                      |
prompt +----------------------------------------------------------------------------+

COLUMN dbid             HEADING 'DB Id'
COLUMN instance_number  HEADING 'Instance Number'
COLUMN startup_time     HEADING 'Startup Time'
COLUMN snap_id          HEADING 'Snap ID'

SELECT
    dbid
  , instance_number
  , TO_CHAR(startup_time, 'DD-MON-YYYY HH24:MI:SS') startup_time
  , snap_id
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)
ORDER BY
    snap_id;


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);

prompt 
prompt 
prompt 
prompt +----------------------------------------------------------------------------+
prompt | Delete from stats$statspack_parameter                                      |
prompt | Dangling statspack parameter rows for the database / instance              |
prompt +----------------------------------------------------------------------------+

COLUMN dbid             HEADING 'DB Id'
COLUMN instance_number  HEADING 'Instance Number'
COLUMN session_id       HEADING 'Session ID'
COLUMN snap_level       HEADING 'Snap Level'

SELECT
    dbid
  , instance_number
  , session_id
  , snap_level
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);


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);


SPOOL off

COMMIT;

EXIT