set verify off
undef schema_name
prompt  Grant required privileges to schema 
prompt  Schema for keeping statspack_setting (Recommend avail schema) :
prompt  &&schema_name
--whenever sqlerror exit sql.sqlcode
begin
  if '&&schema_name' is null then
    raise_application_error(-20101, 'Install failed - No schema_name');
  end if;
end;
/
prompt 
prompt  Granting required privileges ................ 
prompt  

grant execute on perfstat.statspack to &&schema_name;
grant delete  on perfstat.stats$snapshot to &&schema_name;
grant delete  on perfstat.stats$sqltext  to &&schema_name;
grant delete  on perfstat.stats$seg_stat_obj to &&schema_name;
grant delete  on perfstat.stats$undostat to &&schema_name;
grant delete  on perfstat.stats$sql_plan to &&schema_name;
grant delete  on perfstat.stats$database_instance to &&schema_name;
grant delete  on perfstat.stats$statspack_parameter to &&schema_name;
grant select  on perfstat.STATS$SQL_SUMMARY to &&schema_name;
grant select  on perfstat.stats$seg_stat to &&schema_name;
grant select  on perfstat.stats$snapshot to &&schema_name;
grant create trigger to &&schema_name;
grant select on dba_objects to &&schema_name;
grant select on "SYS"."V_$DATABASE" to &&schema_name;
grant select on "SYS"."V_$INSTANCE" to &&schema_name;


prompt 
prompt Creating statspack_setting table ....................
prompt 

drop table &&schema_name..statspack_setting;
create table &&schema_name..statspack_setting
( dbid number not null,
   snap_disable   number(1)    default 0 not null,
   snap_interval  INTERVAL DAY(5) to second (1) not null,
   snap_retention_day  number(3) not null,
   snap_cleanup_scheduled_time  DATE not null,
   snap_cleanup_interval INTERVAL DAY(5) to second (1) not null  
);

ALTER TABLE &&schema_name..STATSPACK_SETTING ADD (CONSTRAINT PKC_STATSPACK_SETTING PRIMARY KEY(DBID));

prompt 
prompt Creating snap gathering procedure ....................
prompt 
CREATE OR REPLACE  PROCEDURE &&schema_name..CRE_SNAP is
begin
  perfstat.statspack.snap;
end;
/
prompt 
prompt Creating snap cleanup procedure ....................
prompt
CREATE OR REPLACE  PROCEDURE &&schema_name..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;
/
prompt 
prompt Creating statspack_setting trigger ....................
prompt 
CREATE OR REPLACE TRIGGER &&schema_name..trg_statspack_setting_aiud BEFORE
INSERT
OR UPDATE
OR DELETE OF DBID, SNAP_CLEANUP_INTERVAL, SNAP_CLEANUP_SCHEDULED_TIME, SNAP_DISABLE, SNAP_INTERVAL, SNAP_RETENTION_DAY ON &&schema_name..statspack_setting FOR EACH ROW
declare
  exp exception;
  myjob number;
  new_interval number;
  new_interval_str varchar2(100);
  new_start_time date;
  databaseid number;
  threshold_min NUMBER:=1; -- 1 minute
begin
  if (deleting) then
       raise_application_error(-20000,'Delete is not allowed ');
  end if;
  if updating('DBID') then
       raise_application_error(-20000,'Update on DBID is not allowed (Old value:'||:old.dbid ||','||'New value:'||:new.dbid||')');
  end if;
  if updating ('SNAP_DISABLE')then
     if (:new.SNAP_DISABLE=0) and (:old.SNAP_DISABLE=1) then  --- Adding statspack gathering/cleanup jobs
          new_interval:=extract (day from :new.SNAP_INTERVAL)*24*60+extract (hour   from :new.SNAP_INTERVAL)*60+extract (minute from :new.snap_interval);
          new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
          dbms_job.submit(myjob,'CRE_SNAP;',SYSDATE+threshold_min/(24*60),new_interval_str);
          new_interval:=extract (day from :new.SNAP_cleanup_INTERVAL)*24*60+extract (hour   from :new.SNAP_cleanup_INTERVAL)*60+extract (minute from :new.snap_cleanup_interval);
          new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
          dbms_job.submit(myjob,'RM_SNAP('||:new.snap_retention_day||');',:new.snap_cleanup_scheduled_time,new_interval_str);
     elsif (:new.SNAP_DISABLE=1) and (:old.SNAP_DISABLE=0) then  --- Remove statspack gathering/cleanup jobs
         select job into myjob from user_jobs where upper(what)='CRE_SNAP;';
         dbms_job.remove(myjob);
         select job into myjob from user_jobs where upper(what) like 'RM_SNAP(%';
         dbms_job.remove(myjob);
     end if;
  ELSE --- If there is no update occured on snap_disable
      if updating ('SNAP_INTERVAL') then
          select job into myjob from user_jobs where upper(what)='CRE_SNAP;';
          new_interval:=extract (day from :new.SNAP_INTERVAL)*24*60+extract (hour   from :new.SNAP_INTERVAL)*60+extract (minute from :new.snap_interval);
          new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
          DBMS_JOB.NEXT_DATE(myjob,SYSDATE+threshold_min/(60*24));
          DBMS_JOB.interval(myjob,new_interval_str);
      end if;
      if  updating ('SNAP_RETENTION_DAY') then
          select job into myjob from user_jobs where upper(what) like 'RM_SNAP(%';
          dbms_job.what(myjob,'RM_SNAP('||:new.snap_retention_day||');');
      end if;
      if updating ('SNAP_CLEANUP_SCHEDULED_TIME' ) THEN
          select dbid into databaseid from v$database;
          IF (databaseid=:new.dbid) and (:new.snap_disable=0) then
            select job into myjob from user_jobs where upper(what) like 'RM_SNAP(%';
            dbms_job.remove(myjob);
            new_interval:=extract (day from :new.SNAP_cleanup_INTERVAL)*24*60+extract (hour   from :new.SNAP_cleanup_INTERVAL)*60+extract (minute from :new.snap_cleanup_interval);
            new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
            dbms_job.submit(myjob,'RM_SNAP('||:new.snap_retention_day||');',:new.snap_cleanup_scheduled_time,new_interval_str);
          END IF;
      end if;
      if updating ('SNAP_CLEANUP_INTERVAL') then
          select job into myjob from user_jobs where upper(what) like 'RM_SNAP(%';
          new_interval:=extract (day from :new.SNAP_cleanup_INTERVAL)*24*60+extract (hour   from :new.SNAP_cleanup_INTERVAL)*60+extract (minute from :new.snap_cleanup_interval);
          new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
          DBMS_JOB.interval(myjob,new_interval_str);
    end if;
  end if;
  if inserting then
     select dbid into databaseid from v$database;
     if(databaseid=:new.dbid) and (:new.snap_disable=0) then
          new_interval:=extract (day from :new.SNAP_INTERVAL)*24*60+extract (hour   from :new.SNAP_INTERVAL)*60+extract (minute from :new.snap_interval);
          new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
          dbms_job.submit(myjob,'CRE_SNAP;',SYSDATE+threshold_min/(60*24),new_interval_str);
          new_interval:=extract (day from :new.SNAP_cleanup_INTERVAL)*24*60+extract (hour   from :new.SNAP_cleanup_INTERVAL)*60+extract (minute from :new.snap_cleanup_interval);
          new_interval_str:='trunc(sysdate,''MI'')+'||new_interval||'/(48*30)';
          dbms_job.submit(myjob,'RM_SNAP('||:new.snap_retention_day||');',:new.snap_cleanup_scheduled_time,new_interval_str);
     elsif(databaseid<>:new.dbid) then
        raise_application_error(-20000,'DBID value does not match with current database ');
     end if;
  end if;
end;
/

prompt 
prompt Compiling trigger .....
prompt 

Alter trigger &&schema_name..trg_statspack_setting_aiud compile;

prompt
prompt Compiling procedures ....
prompt 

alter procedure &&schema_name..CRE_SNAP compile;
alter procedure &&schema_name..RM_SNAP compile;