1 Reply Latest reply: Jul 3, 2014 7:23 AM by Srini Chavali-Oracle RSS

    Sniper job not able to stop all the jobs runs more than 2 minutes

    976208

      Hi All,

       

       

      I have created master job(The master job runs every hour.) that is dynamically creating child jobs

      and once the child job is completing it will be doping automatically.

       

      Now I have set timeout for each child job.

      If any child job is running for more than 2 minutes it should be stopped.

       

      Timeout is not working for all the job which are running for more than 2 minutes.

       

       

      The sniper job is stopping only 1 or 2 jobs.

       

       

      The sniper job is not triggering for all the jobs which are running for morethan 2 minutes.

       

      CREATE OR REPLACE PROCEDURE DYNAMIC_JOB_SCHEDULER

      IS

      BEGIN

      FOR CUR_SCH IN

      (SELECT sl.OWNER_NAME,sl.BU_CODE,sl.SCH_ID

      FROM SCHEMA_LIST sl,

      SCHEMA_CONFIGURATION SC

      WHERE sl.SCH_ID = SC.SCH_ID

      AND SC.ACTIVE = 'Y')

      LOOP          

      BEGIN

         dbms_scheduler.create_job (

            job_name        => 'DYN_JOB'||CUR_SCH.OWNER_NAME,

            job_class       =>  CUR_SCH.OWNER_NAME,

            job_type        => 'PLSQL_BLOCK',

            job_action      => 'BEGIN DAILY_SALES_PKG.SALE_REV('||CUR_SCH.SCH_ID||'); END;',

            auto_drop       => TRUE,

            comments        => 'Fetching job for '||CUR_SCH.OWNER_NAME,

            enabled         => TRUE

            );

        DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'DYN_JOB'||CUR_SCH.OWNER_NAME

                                        ,attribute => 'max_run_duration'

                                        ,value =>  interval '120' second);

      END;

      END LOOP;

      EXCEPTION

         WHEN OTHERS

         THEN

         RAISE_APPLICATION_ERROR(-20001,SQLERRM);

      END DYNAMIC_JOB_SCHEDULER;

      /

       

       

      BEGIN

        SYS.DBMS_SCHEDULER.CREATE_JOB

          (

             job_name        => 'DYN_MASTER_JOB'

            ,start_date      =>  SYSTIMESTAMP

            ,repeat_interval => 'FREQ=HOURLY;BYMINUTE=0; BYSECOND=0;'

            ,end_date        =>  NULL

            ,job_class       => 'DEFAULT_JOB_CLASS'

            ,job_type        => 'STORED_PROCEDURE'

            ,job_action      => 'DYNAMIC_JOB_SCHEDULER'

            ,comments        => 'master job runs every hour'

            ,enabled         => TRUE

          );

      END;

       

       

      begin

      dbms_scheduler.add_event_queue_subscriber('myagent');

      end;

      /

       

       

      create or replace procedure sniper_proc (message IN sys.scheduler$_event_info) as

      v_autodrop varchar2(30);

      begin

      if message.event_type != 'JOB_OVER_MAX_DUR' then

      raise PROGRAM_ERROR;

      end if;

            dbms_scheduler.drop_job('"'||message.object_owner||'"."'||message.object_name ||'"',force=>TRUE);

               insert into job_output values (systimestamp,

           'sniper job sniped '||'"'||message.object_owner||'"."'||

            message.object_name ||'"');

      end;

      /

       

       

       

       

      begin

          dbms_scheduler.create_program (

              program_name => 'sniper_prog',

              program_action=> 'sniper_proc',

              program_type => 'stored_procedure',

              number_of_arguments => 1,

              enabled => FALSE) ;

       

          dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1);

          dbms_scheduler.enable('sniper_prog');

      end;

      /

       

      begin

          dbms_scheduler.create_job('sniper_job',

            program_name=>'sniper_prog',

            event_condition =>

              'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',

            queue_spec =>'sys.scheduler$_event_queue,myagent',

            enabled=>true);

      end;

      /

       

      Please help me.

       

      Thanks.