oracle – Statspack setup

exec statspack.modify_statspack_parameter(i_snap_level=>7)

BEGIN 
  SYS.DBMS_SCHEDULER.CREATE_JOB 
    ( 
       job_name        => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
      ,start_date      => TO_TIMESTAMP_TZ('2014/06/16 10:00:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzr') 
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=60' 
      ,end_date        => NULL 
      ,job_class       => 'DEFAULT_JOB_CLASS' 
      ,job_type        => 'STORED_PROCEDURE' 
      ,job_action      => 'PERFSTAT.STATSPACK.SNAP' 
      ,comments        => 'Collect Statspack statistics every 60 min' 
    ); 
    
 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'RESTARTABLE' 
     ,value     => FALSE); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'LOGGING_LEVEL' 
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'MAX_FAILURES'); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'MAX_RUNS'); 
  
 BEGIN 
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
      ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
       ,attribute => 'STOP_ON_WINDOW_CLOSE' 
       ,value     => FALSE); 
  EXCEPTION 
    -- could fail if program is of type EXECUTABLE... 
    WHEN OTHERS THEN 
      NULL; 
  END; 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'JOB_PRIORITY' 
     ,value     => 3); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'SCHEDULE_LIMIT'); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_COLLECTION_JOB' 
     ,attribute => 'AUTO_DROP' 
     ,value     => FALSE); 
 
  SYS.DBMS_SCHEDULER.ENABLE 
    (name                  => 'PERFSTAT.STATSPACK_COLLECTION_JOB'); 
END; 
 
/

BEGIN 
  SYS.DBMS_SCHEDULER.CREATE_JOB 
    ( 
       job_name        => 'PERFSTAT.STATSPACK_DELETION_JOB' 
      ,start_date      => TO_TIMESTAMP_TZ('2014/06/16 11:00:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzr') 
      ,repeat_interval => 'FREQ=HOURLY;INTERVAL=24' 
      ,end_date        => NULL 
      ,job_class       => 'DEFAULT_JOB_CLASS' 
      ,job_type        => 'PLSQL_BLOCK' 
      ,job_action      => 'Begin 
perfstat.statspack.purge(I_PURGE_BEFORE_DATE=>sysdate-14); 
end 
;' 
      ,comments        => 'Deletes Statspack Snaps every 24h older than 14 Days' 
    ); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'RESTARTABLE' 
     ,value     => FALSE); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'LOGGING_LEVEL' 
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'MAX_FAILURES'); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'MAX_RUNS'); 
   
BEGIN 
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
      ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
       ,attribute => 'STOP_ON_WINDOW_CLOSE' 
       ,value     => FALSE); 
  EXCEPTION 
    -- could fail if program is of type EXECUTABLE... 
    WHEN OTHERS THEN 
      NULL; 
  END; 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'JOB_PRIORITY' 
     ,value     => 3); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'SCHEDULE_LIMIT'); 
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE 
    ( name      => 'PERFSTAT.STATSPACK_DELETION_JOB' 
     ,attribute => 'AUTO_DROP' 
     ,value     => FALSE); 
 
  SYS.DBMS_SCHEDULER.ENABLE 
    (name                  => 'PERFSTAT.STATSPACK_DELETION_JOB'); 
END; 
/