Scheduled Job That Works Does Not Start In Window (Doc ID 742683.1)

Last updated on AUGUST 05, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2016***

Symptoms

You have created a job for example using the following code:

-- Create weeknight window.  Weeknight window is 2am - 5am Tue - Sat.
    BEGIN
       BEGIN
       dbms_scheduler.create_window(
          window_name=>'ITEM_STATS_WINDOW',
          resource_plan=>NULL,
          repeat_interval=>'freq=daily;byday=TUE,WED,THU,FRI,SAT;byhour=02;' ||
                        'byminute=0; bysecond=0',
          duration=>interval '180' minute,
          comments=>'Window for ITEM statistics task');
       EXCEPTION
          when others then
            if sqlcode = -27477 then NULL;
            else raise;
            end if;
       END;
       dbms_scheduler.set_attribute('ITEM_STATS_WINDOW','SYSTEM',TRUE);
       dbms_scheduler.set_attribute('ITEM_STATS_WINDOW',
                                     'FOLLOW_DEFAULT_TIMEZONE',TRUE);
    EXCEPTION
          when others then
            if sqlcode = -27477 then NULL;
            else raise;
            end if;
    END;
    /
    
    -- Create stored procedure to run the ITEM statistics collection.
    create or replace PROCEDURE gather_item_stats IS
    BEGIN
          dbms_stats.gather_table_stats('ASSREQS','ITEM',cascade=>true);
    END;
    /
    
    -- Create gather ITEM stats program.
    BEGIN
    dbms_scheduler.create_program(
      program_name=>'gather_item_stats_prog',
      program_type=>'STORED_PROCEDURE',
      program_action=>'gather_item_stats',
      number_of_arguments=>0,
      enabled=>TRUE,
      comments
          =>'Gather daily statistics for ASSREQS.ITEM table and indexes');
    EXCEPTION
      when others then
        if sqlcode = -27477 then NULL;
        else raise;
        end if;
    END;
    /
    
    -- Create ITEM stats collection job
    BEGIN
        BEGIN
        dbms_scheduler.create_job(
          job_name=>'gather_item_stats_job',
          program_name=>'gather_item_stats_prog',
          job_class=>'auto_tasks_job_class',
          schedule_name=>'ITEM_STATS_WINDOW',
          enabled=>TRUE,
          auto_drop=>FALSE,
          comments
              =>'Gather daily statistics for ASSREQS.ITEM table and indexes');
        EXCEPTION
          when others then
            if sqlcode = -27477 then NULL;
            else raise;
            end if;
        END;
        dbms_scheduler.set_attribute('gather_item_stats_job','stop_on_window_close',
                 true);
        dbms_scheduler.set_attribute('gather_item_stats_job','restartable', true);
        dbms_scheduler.set_attribute('gather_item_stats_job',
            'user_operations_callback','dbms_stats.cleanup_stats_job_proc');
        dbms_scheduler.set_attribute('gather_item_stats_job','user_callback_context',1);
    EXCEPTION
      when others then
        if sqlcode = -27477 then NULL;
        else raise;
        end if;
    END;


The job executes fine when invoked manually using

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms