定期清除STATSPACK舊的資料

1.創建PACKAGE:

CREATE OR REPLACE PACKAGE statspack_admin AS

  PROCEDURE purge_older_than_days(days      IN INTEGER,
                                  area_size IN INTEGER DEFAULT NULL);

  /*
  -- submit a job to run every day at 3am 
  deleting snaps older than 30 days
  -- specifying 50Mb for PGA.
  
  DECLARE
  j BINARY_INTEGER;
  BEGIN
  DBMS_JOB.SUBMIT(j, 'statspack_admin.purge_older_than_days(30, 52428800);', 
  TRUNC(SYSDATE) (3/24), 'TRUNC(SYSDATE) 1 (3/24)' );
  COMMIT;
  END;
  
  */

  PROCEDURE purge(lo_snap   IN NUMBER,
                  hi_snap   IN NUMBER,
                  area_size IN INTEGER DEFAULT NULL);

  PROCEDURE move_tablespace(tablespace_name IN VARCHAR2,
                            window_in_hours IN NUMBER);

END statspack_admin;
/
CREATE OR REPLACE PACKAGE BODY statspack_admin AS
  on_9i     BOOLEAN := FALSE;
  dbversion VARCHAR2(512);
  dbcompat  VARCHAR2(512);

  PROCEDURE purge(lo_snap   IN NUMBER,
                  hi_snap   IN NUMBER,
                  area_size IN INTEGER DEFAULT NULL) IS
  
    dbid      v$database.dbid%TYPE;
    inst_num  v$instance.instance_number%TYPE;
    inst_name v$instance.instance_name%TYPE;
    db_name   v$database.name%TYPE;
    btime     DATE;
    etime     DATE;
  
  BEGIN
  
    SELECT d.dbid            AS dbid,
           i.instance_number AS inst_num,
           i.instance_name   AS inst_name,
           d.name            AS db_name
      INTO dbid, inst_num, inst_name, db_name
      FROM v$database d, v$instance i;
  
    select snap_time
      into btime
      from stats$snapshot b
     where b.snap_id = (SELECT MIN(x.snap_id)
                          FROM stats$snapshot x
                         WHERE x.snap_id >= lo_snap)
       and b.dbid = dbid
       and b.instance_number = inst_num;
  
    select snap_time
      into etime
      from stats$snapshot e
     where e.snap_id = (SELECT MAX(x.snap_id)
                          FROM stats$snapshot x
                         WHERE x.snap_id <= hi_snap)
       and e.dbid = dbid
       and e.instance_number = inst_num;
  
    IF on_9i AND area_size IS NOT NULL THEN
      EXECUTE IMMEDIATE 'alter session set workarea_size_policy=MANUAL';
      EXECUTE IMMEDIATE 'alter session set hash_area_size=' ||
                        TO_CHAR(area_size);
      EXECUTE IMMEDIATE 'alter session set sort_area_size=' ||
                        TO_CHAR(area_size);
    ELSIF area_size IS NOT NULL THEN
      EXECUTE IMMEDIATE 'alter session set hash_area_size=' ||
                        TO_CHAR(area_size);
      EXECUTE IMMEDIATE 'alter session set sort_area_size=' ||
                        TO_CHAR(area_size);
    END IF;
  
    delete from stats$snapshot
     where instance_number = inst_num
       and dbid = dbid
       and snap_id between lo_snap and hi_snap;
  
    /*-- Delete any dangling SQLtext 
    -- The following statement deletes any dangling SQL statements which
    -- are no longer referred to by ANY snapshots. This statment has been
    -- commented out as it can be very resource intensive. 
    --*/
    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));
  
    -- Adding an optional STATS$SEG_STAT_OBJ delete statement
    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));
  
    /* 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 < btime
       and end_time > etime;
  
    /* Delete any dangling database 
    nstance 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 purge;

  /* procedure to move tablespaces */
  PROCEDURE move_tablespace(tablespace_name IN VARCHAR2,
                            window_in_hours IN NUMBER) IS
    ts DATE := SYSDATE;
    te DATE := ts(window_in_hours / 24);
  BEGIN
  
    -- do the tables that haven't been rebuilt recently first
    FOR t IN (SELECT object_name AS table_name
                FROM user_objects
               WHERE object_type = 'TABLE'
               ORDER BY last_ddl_time ASC) LOOP
    
      EXECUTE IMMEDIATE 'ALTER TABLE ' || t.table_name ||
                        ' MOVE TABLESPACE ' || tablespace_name;
    
      -- now immediately rebuild the indexes
      -- I could use dbms_job to do this 
      -- asynchronously, maybe in a future revision ?
      -- not using ONLINE because moving 
      -- tables cannot be done ONLINE !
      -- maybe use dbms_redef in future version? 
      FOR i IN (SELECT index_name
                  FROM user_indexes
                 WHERE table_name = t.table_name) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' 
REBUILD TABLESPACE ' || tablespace_name;
      END LOOP;
    
      IF SYSDATE > te THEN
        EXIT;
      END IF;
    
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      IF dbms_job.is_jobq THEN
        /*
        remove the  job preventing nasty 
        reruns outside the window 
        but generating a trace file for the DBA 
        */
        DBMS_JOB.REMOVE(NVL(SYS_CONTEXT('USERENV', 'BG_JOB_ID'),
                            SYS_CONTEXT('USERENV', 'FG_JOB_ID')));
        COMMIT;
      END IF;
      RAISE;
  END move_tablespace;

  /* purge records older than X days */
  PROCEDURE purge_older_than_days(days      IN INTEGER,
                                  area_size IN INTEGER DEFAULT NULL) IS
  
    losnap stats$snapshot.SNAP_ID%TYPE;
    hisnap stats$snapshot.SNAP_ID%TYPE;
  
  BEGIN
  
    SELECT s.snap_id
      INTO hisnap
      FROM stats$snapshot s
     WHERE s.snap_id =
           (SELECT MAX(s.snap_id)
              FROM stats$snapshot s
             WHERE s.snap_time < TRUNC(SYSDATE) - days);
  
    SELECT s.snap_id
      INTO losnap
      FROM stats$snapshot s
     WHERE s.snap_id = (SELECT MIN(s.snap_id)
                          FROM stats$snapshot s
                         WHERE s.snap_id <= hisnap);
    -- in case the highest snap is the only one to delete
  
    --DBMS_OUTPUT.PUT_LINE('Lo Snap: '||TO_CHAR(losnap));
    --DBMS_OUTPUT.PUT_LINE('Hi Snap: '||TO_CHAR(hisnap));
  
    purge(losnap, hisnap, area_size);
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
      -- select into hisnap returned no rows, 
    --which means no snaps are older than days specified
  
  END purge_older_than_days;

BEGIN

  SYS.DBMS_UTILITY.DB_VERSION(dbversion, dbcompat);

  IF TO_NUMBER(REPLACE(dbversion, '.')) >= 90000 THEN
    -- we are on a 9i DB 
    on_9i := TRUE;
  ELSE
    on_9i := FALSE;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    on_9i := FALSE;
  
END statspack_admin;
/

 

2.排程:

DECLARE
  j BINARY_INTEGER;
BEGIN
  DBMS_JOB.SUBMIT(j,
                  'statspack_admin.purge_older_than_days
(30, 52428800);',
                  TRUNC(SYSDATE) (3 / 24),
                  'TRUNC(SYSDATE) 1 (3/24)');
  COMMIT;
END;