--$Header: Version: 4 Last Change by: TPGSHERVIN 20060726 13:21:42 $
-- Copyright 2006, The Pythian Group, Inc. All rights reserved.
-- Updates not made via TPG's AVAILCONFIG schema will be lost!!
set linesize 500 verify off
set serveroutput on
var cnt number;
prompt;
prompt STATSPACK CHECKING;
prompt ------------------;
declare
   trg_exist  number;
   trg_enable number;
   stats_tbl_exist number:=0;
   mydbid     number;
   snapdis number;
   undeletesnap number;
   snap_job number:=0;
   snapcleaninterv number;
   snapretention  number;
   tmpstr varchar2(1000);
begin
  :cnt:=250;
  ------------- Check Triggers
  begin
        select 1 into trg_exist from user_triggers where trigger_name='TRG_STATSPACK_SETTING_AIUD' ;
        dbms_output.put_line         (' Trigger Exists ........................................................  [OK] ');
        exception when no_data_found then
            dbms_output.put_line     (' Trigger does not exist on ............................................  [Error]' );
            :cnt:=1;
  end;
  begin
        select 1 into trg_enable from user_triggers where trigger_name='TRG_STATSPACK_SETTING_AIUD' and status='ENABLED' ;
        dbms_output.put_line         (' Trigger is Enable ...................................................... [OK] ');
        exception when no_data_found then
           dbms_output.put_line      (' Trigger is not Enable on ............................................... [Error]');
           :cnt:=1;
  end;
  ------------- Check STTASPACK_SETTING
  begin
        select 1 into stats_tbl_exist from user_tables where table_name='STATSPACK_SETTING' ;
        dbms_output.put_line         (' STATSPACK_SETTING table exists ..........................................[OK] ');
        exception when no_data_found then
           dbms_output.put_line      (' STATSPACK_SETTING does not exist  .......................................[Error]');
           :cnt:=1;
  end;
  ------------- If STATSPACK_SETTING exists ,It will check STATSPACK configuration to be matched with settings
  if(stats_tbl_exist=1) then
        select dbid into mydbid from v$database;
        begin
        select snap_disable,snap_retention_day,extract(day from snap_cleanup_interval)+extract(hour from snap_cleanup_interval)/24+extract(minute from snap_cleanup_interval)/(24*60) into snapdis,snapretention,snapcleaninterv from statspack_setting where dbid=mydbid;
         dbms_output.put_line        ('This instance is using STATSPACK_SETTING ................................[OK]');
         if(snapdis=1) then  ---- If snaps gathering is not active
             tmpstr:='RM_SNAP('||snapretention||');';
             begin
              select 1 into snap_job from user_jobs where what='CRE_SNAP;' or what=tmpstr;
              dbms_output.put_line   ('Statspack Job is out of sync with STATSPACK_SETTING  ................... [Error]');
              :cnt:=1;
              exception when no_data_found then
                 dbms_output.put_line('No Statspack Jobs ,sync with STATSPACK_SETTING ......................... [OK]');
             end;
          end if; --- end of snapdis=1
          If(snapdis=0) then  ---- If snaps gathering is active
            tmpstr:='RM_SNAP('||snapretention||');';
            begin
              select 1 into snap_job from user_jobs where what='CRE_SNAP;';
              dbms_output.put_line   ('Statspack gathering job exists ,sync with STATSPACK_SETTING .............[OK]');
              exception when no_data_found then
                 dbms_output.put_line('No statspack gathering job exists ,out of sync with STATSPACK_SETTING ...[Error]');
                 :cnt:=1;
            end;
            begin
              select 1 into snap_job from user_jobs where what=tmpstr;
              dbms_output.put_line   ('Statspack cleanup job exists ,sync with STATSPACK_SETTING ...............[OK]');
             exception when no_data_found then
                 dbms_output.put_line('No statspack cleanup job exists ,out of sync with STATSPACK_SETTING .....[Error]');
                 :cnt:=1;
            end;
          end if; --- End of snapdis=0
          ------------ Check cleanup
          select count(*) into undeletesnap from perfstat.stats$snapshot where SNAP_TIME<trunc(sysdate-snapretention-snapcleaninterv,'DD');
         if(undeletesnap<>0) then
           dbms_output.put_line      ('Snaps cleanup does not work properly ................................... [Error]');
           :cnt:=1;
         else
           dbms_output.put_line      ('Snaps cleanup works properly ........................................... [OK]');
         end if;
       exception when no_data_found then  ---- If there is not record in statspack_setting for this instance
           dbms_output.put_line      ('This instance is not using STATSPACK_SETTING ........................... [Error] ');
           :cnt:=1;
       end;
  end if; --- end of if stats_tb_exist=1
end;