DECLARE
  lv_sql VARCHAR2(4000);
  v_env_type               VARCHAR2(30) := '&&1';
  v_db_version             VARCHAR2(30) := '&2';
  v_instance               VARCHAR2(30)  := '&3';
BEGIN

   DBMS_OUTPUT.PUT_LINE('###########################################');
   DBMS_OUTPUT.PUT_LINE('Job Started at ' || to_char(sysdate,'DD-MON-YYYY hh24:MI:SS') );


   FOR i in (SELECT DISTINCT dbid
                  , c.database
                  , instance_number
                  , c.keep_days
               FROM stats$database_instance i
                  , perfstat_control.statspack_merge_control c
              WHERE c.database = i.db_name
                AND c.database = NVL(v_instance,c.database)
                AND c.version  = v_db_version
                AND c.purge_active = 'Y') 
   LOOP

        DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
        DBMS_OUTPUT.PUT_LINE(i.database  || to_char(sysdate,'DD-MON-YYYY hh24:MI:SS'));

        /*--------------------------------------------------------
        Delete all snapshots except for midnight ones older thans keep_days
        retain  all midnight snapshots
        ----------------------------------------------------------*/
        DELETE
          FROM stats$snapshot
         WHERE dbid = i.dbid
           AND instance_number = i.instance_number
           AND snap_time < sysdate - i.keep_days
           AND to_char(snap_time,'HH24MI') NOT between '0000' and '0010'
           ;

        commit;


        /*---------------------------------------------------------------------
        Delete all SQL captured which are below set threshold 
        This manual cleanup is required in historical data in case thresholds 
        have been increased to reduce the SQLs captured.
        -----------------------------------------------------------------------*/
        if v_db_version = '9.2'
        then

            lv_sql := 'DELETE FROM stats$sql_summary
            WHERE(snap_id,   dbid,   instance_number,   hash_value,   text_subset) IN
              (SELECT ss.snap_id,
                          ss.dbid,
                  ss.instance_number,
                  ss.hash_value,
                  ss.text_subset
                FROM stats$sql_summary ss,
                  stats$statspack_parameter sp
                WHERE ss.dbid = sp.dbid
                AND ss.instance_number = sp.instance_number
                AND ss.dbid = ' || i.dbid || '
                AND ss.instance_number = ' || i.instance_number || '
                AND ss.executions < sp.executions_th *1
                AND ss.parse_calls < sp.parse_calls_th *1
                AND ss.disk_reads < sp.disk_reads_th *1
                AND ss.buffer_gets < sp.buffer_gets_th *1
                AND ss.sharable_mem < sp.sharable_mem_th *1
                AND ss.version_count < sp.version_count_th *1)
             ';

            EXECUTE IMMEDIATE lv_sql;   
            COMMIT;

            lv_sql := 'DELETE  /*+ index(spu )*/
            FROM stats$sql_plan_usage spu
            WHERE NOT EXISTS
               (SELECT 1
                FROM stats$sql_summary ss
                WHERE ss.dbid = spu.dbid
                AND ss.instance_number = spu.instance_number
                AND ss.dbid = ' || i.dbid || '
                AND ss.instance_number = ' || i.instance_number || '
                AND ss.snap_id = spu.snap_id
                AND ss.hash_value = spu.hash_value
                AND ss.text_subset = spu.text_subset)
             ';

            EXECUTE IMMEDIATE lv_sql;   
            COMMIT;

         end if; -- 9i

        
        if v_db_version = '10.2'
        then

            lv_sql := 'DELETE FROM stats$sql_summary
            WHERE(snap_id,   dbid,   instance_number,   old_hash_value,   text_subset) IN
              (SELECT ss.snap_id,
                          ss.dbid,
                  ss.instance_number,
                  ss.old_hash_value,
                  ss.text_subset
                FROM stats$sql_summary ss,
                  stats$statspack_parameter sp
                WHERE ss.dbid = sp.dbid
                AND ss.instance_number = sp.instance_number
                AND ss.dbid = ' || i.dbid || '
                AND ss.instance_number = ' || i.instance_number || '
                AND ss.executions < sp.executions_th *1
                AND ss.parse_calls < sp.parse_calls_th *1
                AND ss.disk_reads < sp.disk_reads_th *1
                AND ss.buffer_gets < sp.buffer_gets_th *1
                AND ss.sharable_mem < sp.sharable_mem_th *1
                AND ss.version_count < sp.version_count_th *1)
             ';
             
            EXECUTE IMMEDIATE lv_sql;   
            COMMIT;
             
            lv_sql := 'DELETE  /*+ index(spu )*/
            FROM stats$sql_plan_usage spu
            WHERE NOT EXISTS
               (SELECT 1
                FROM stats$sql_summary ss
                WHERE ss.dbid = spu.dbid
                AND ss.instance_number = spu.instance_number
                AND ss.dbid = ' || i.dbid || '
                AND ss.instance_number = ' || i.instance_number || '
                AND ss.snap_id = spu.snap_id
                AND ss.old_hash_value = spu.old_hash_value
                AND ss.text_subset = spu.text_subset)
             ';
             
            EXECUTE IMMEDIATE lv_sql;   
            COMMIT;

         end if; -- 10g

   END LOOP; 


-- if iextended_purge then
         --
         -- Purge segments which do not have RI constraints to stats$snapshot

        if v_db_version = '9.2'
        then

             -- 
             -- SQL text
             -- 
             lv_sql := 'delete from stats$sqltext st1
                       where (st1.hash_value, st1.text_subset) in
                                (select /*+ index_ffs(st) */
                                        st.hash_value, st.text_subset
                                   from stats$sqltext st
                                  where (hash_value, text_subset) not in
                                            (select /*+ hash_aj index_ffs(ss) */
                                                    distinct hash_value, text_subset
                                               from stats$sql_summary ss
                                            )
                                    and st.piece = 0
                             )';
             EXECUTE IMMEDIATE lv_sql;   
             commit;
            
             -- 
             -- SQL execution plans
             -- 
             lv_sql := 'delete from stats$sql_plan sp1
                       where sp1.plan_hash_value in
                                (select /*+ index_ffs(sp) */
                                        sp.plan_hash_value
                                   from stats$sql_plan sp
                                  where plan_hash_value not in
                                            (select /*+ hash_aj */
                                                    distinct plan_hash_value
                                               from stats$sql_plan_usage spu
                                            )
                                    and sp.id = 0
                                )';
             EXECUTE IMMEDIATE lv_sql;   
             commit;
             
             
             -- 
             -- Segment Identifiers
             -- 
             lv_sql := 'delete /*+ index_ffs(sso) */
                        from stats$seg_stat_obj sso
                       where (dbid, dataobj#, obj#, ts#) not in
                             (select /*+ hash_aj full(ss) */
                                     dbid, dataobj#, obj#, ts#
                                from stats$seg_stat ss
                             )';
             EXECUTE IMMEDIATE lv_sql;   
             commit;
            
        end if; -- is it 9i


        if v_db_version = '10.2'
        then

             --
             -- SQL Text
             --
             lv_sql:= 'delete from stats$sqltext st1
                         where (st1.old_hash_value, st1.text_subset) in
                                  (select /*+ index_ffs(st) */
                                          st.old_hash_value, st.text_subset
                                     from stats$sqltext st
                                    where (old_hash_value, text_subset) not in
                                              (select /*+ hash_aj index_ffs(ss) */
                                                      distinct old_hash_value, text_subset
                                                 from stats$sql_summary ss
                                              )
                                      and st.piece = 0
                               )';
             EXECUTE IMMEDIATE lv_sql;   
             commit;
    
             --
             -- SQL execution plans
             --
             lv_sql := 'delete from stats$sql_plan sp1
                        where sp1.plan_hash_value in
                                  (select /*+ index_ffs(sp) */
                                          sp.plan_hash_value
                                     from stats$sql_plan sp
                                    where plan_hash_value not in
                                              (select /*+ hash_aj */
                                                      distinct plan_hash_value
                                                 from stats$sql_plan_usage spu
                                              )
                                      and sp.id = 0
                                  )';
             EXECUTE IMMEDIATE lv_sql;   
             commit;
    
    
             --
             -- Segment Identifiers
             --
             lv_sql := 'delete /*+ index_ffs(sso) */
                          from stats$seg_stat_obj sso
                         where (dbid, dataobj#, obj#, ts#) not in
                               (select /*+ hash_aj full(ss) */
                                       dbid, dataobj#, obj#, ts#
                                  from stats$seg_stat ss
                               )';
             EXECUTE IMMEDIATE lv_sql;   
             commit;
    
         end if; -- Is it 10g

--  end if; -- extended purge

    DBMS_OUTPUT.PUT_LINE('###########################################');
    DBMS_OUTPUT.PUT_LINE('Job Finished at ' || to_char(sysdate,'DD-MON-YYYY hh24:MI:SS') );

END;
/