column job format 9999
column log_user format a8
column priv_user format a8
column schema_user format a8
column last format a8
column b format a1
column fail format 999
column what format a70

set serveroutput on
exec dbms_output.enable(1000000);
begin
 for i in  ( select job from dba_jobs
             where substr(what,1,11)='ash.collect'
               or
                   substr(what,1,11)='ash.get_all'
             ) loop
     dbms_output.put_line( 'rdbms_job.remove ' || i.job );
     dbms_job.remove( i.job );
  end loop;
end;
/


-- insert into ash_data_v every sec for an hour
-- Oracle job runs this every hour

   variable job number

   spool ashstop.sql
   begin
         dbms_job.submit( job       => :job
                        ,what      => 'ash.collect(3,1200);'
                        ,next_date => sysdate
                        ,interval  => 'trunc(sysdate+(1/(24)),''HH'')'
                        );
   end;
/
--   select 'exec dbms_job.remove('||to_char(:job)||');' from dual;

   begin
        dbms_job.submit(:job,
                        'ash.get_all;',
                        sysdate,
                        'trunc(sysdate+(1/(24)),''HH'')'
                        );
   end;
/
--   select 'exec dbms_job.remove('||to_char(:job)||');' from dual;
   spool off

   commit;
select job, log_user,priv_user, schema_user, last_sec,
       this_sec,next_sec, broken b, failures fail, total_time, what
from dba_jobs
where substr(what,1,11)='ash.collect'
  or
      substr(what,1,11)='ash.get_all'
/