CREATE OR REPLACE PROCEDURE rm_snap (retention number)
is
dbid_var       number;
inst_num_var   number;
inst_name_var  varchar2(20);
db_name_var    varchar2(20);
begin
 select instance_number into inst_num_var from v$instance;
  select dbid,name into dbid_var,db_name_var from v$database;

--- Detele snapshots older than retention

  delete from perfstat.stats$snapshot
         where
          instance_number = inst_num_var
          and
          dbid            = dbid_var
          and
          snap_time < trunc(sysdate-retention,'DD');


---Delete any dangling SQLtext

  delete  from perfstat.stats$sqltext st
          where (hash_value, text_subset) not in
          (select /*+ hash_aj */  hash_value, text_subset from perfstat.STATS$SQL_SUMMARY ss);



--- Delete any dangling SEGMENT STAT
   delete from perfstat.stats$seg_stat_obj sso
          where (dbid, dataobj#, obj#) not in
         (select /*+ hash_aj  */ dbid, dataobj#, obj# from perfstat.stats$seg_stat ss);


--- Delete any dangling undostat
   delete from perfstat.stats$undostat
          where
          snap_id not in (select /*+ hash_aj */ snap_id from perfstat.stats$snapshot);


--- Delete any dangling SQLplan
   delete from perfstat.stats$sql_plan us
          where
          snap_id not in (select /*+ hash_aj */ snap_id from perfstat.stats$snapshot);

--- Delete any dangling statspack database instance
   delete from perfstat.stats$database_instance di
          where
             instance_number = inst_num_var
          and
             dbid= dbid_var
          and
            not exists (select /*+ hash_aj */ 1  from perfstat.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
     delete from perfstat.stats$statspack_parameter sp
            where instance_number = inst_num_var
                  and dbid            = dbid_var
                  and not exists (select /*+ hash_aj */ 1
                     from perfstat.stats$snapshot s
                    where s.dbid            = sp.dbid
                      and s.instance_number = sp.instance_number);
end;