Sp purge 30days 9i

Fiche express
Type	Script SQL*Plus de diagnostic (Statspack)
Voir aussi	Sp purge 30days 10g · Sp purge

Sp purge 30days 9i supprime les snapshots Statspack antérieurs à un nombre de jours donné, pour les versions Oracle 9i qui ne disposent pas encore de la fonction statspack.purge (apparue en 10g, voir Sp purge 30days 10g).

Le script reproduit manuellement la logique de sppurge.sql : calcul de la plage de snap_id à supprimer d'après leur ancienneté, puis suppression en cascade dans les tables du repository (stats$snapshot, stats$sqltext, stats$seg_stat_obj, stats$undostat, stats$database_instance, stats$statspack_parameter).

Script

-- Supprime les snapshots Statspack antérieurs à &days_to_keep jours
-- (Oracle 9i, sans statspack.purge : suppression manuelle table par table).

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

DEFINE days_to_keep = 30

WHENEVER SQLERROR EXIT ROLLBACK

SPOOL sp_purge_&days_to_keep._days_9i.lis

prompt Récupération de la base et de l'instance courantes :

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

prompt Bornes (min/max snap_id) des snapshots plus vieux que &days_to_keep jours :

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

prompt Snapshots qui vont être supprimés pour cette instance :

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
    snap_id
/

prompt Récupération des bornes de temps (utilisées pour purger stats$undostat) :

COLUMN btime NEW_VALUE btime
COLUMN etime NEW_VALUE etime

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

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

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

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

prompt Suppression des snapshots &&losnapid a &&hisnapid :

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

prompt Suppression des textes SQL devenus orphelins (aucun snapshot restant ne les référence) :

DELETE FROM stats$sqltext st
 WHERE (hash_value, text_subset) NOT IN (
     SELECT 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)
   );

prompt Suppression des statistiques de segments devenues orphelines :

DELETE FROM stats$seg_stat_obj sso
 WHERE (dbid, dataobj#, obj#) NOT IN (
     SELECT 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 Suppression des lignes stats$undostat couvrant la période purgée :

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

prompt Suppression des lignes stats$database_instance devenues orphelines :

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 Suppression des lignes stats$statspack_parameter devenues orphelines :

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

Voir aussi

• Sp purge 30days 10g — équivalent 10g, basé sur statspack.purge
• Sp purge — script d'accueil (purge manuelle par plage de snap_id)
• Sp list — repérer les snap_id concernés avant purge