Scheduler Job Runs Immediately After Enable When Using PL/SQL-expression

刘泽宇

现象：
A job created using DBMS_SCHEDULER, runs automatically after it is enabled, when REPEAT_INTERVAL is calculated by a function (repeat_interval =>'nextExecution',).

The job runs after it is enabled even if the NEXT_RUN_DATE is in the future.

1) Create a table named testJobTab

SQL> create table testJobTab ( insertdate date);

2) Create a procedure to insert into the table

SQL> create or replace procedure testJobinsert

is

BEGIN

insert into testJobTab (insertdate) values ( sysdate);

END;

/

3) Create a function to calculate the date

SQL> create or replace function nextExecution

return date

is

BEGIN

return trunc(sysdate + 10);

END;

/

4) make sure the table is empty

SQL> truncate table testJobTab;

5) Create a job (disabled)

SQL> BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'TESTJOB',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN testJobinsert; commit; END;',

start_date => systimestamp at TIME zone 'EUROPE/BERLIN',

repeat_interval => 'nextExecution',

end_date => NULL,

enabled => FALSE,

comments => 'TESTJOB'

);

END;

/

6) check the table

SQL> select * from testJobTab;

no rows selected

7) check user_scheduler_jobs

SQL> set lines 300

SQL> col start_date for a50

SQL> col last_start_date for a50

SQL> col next_run_date for a50

SQL> col repeat_interval for a25

SQL> select start_date,run_count,last_start_date,next_run_date,enabled,schedule_type,repeat_interval from user_scheduler_jobs where job_name = 'TESTJOB';

START_DATE RUN_COUNT LAST_START_DATE

NEXT_RUN_DATE

ENABL SCHEDULE_TYP REPEAT_INTERVAL

-------------------------------------------------- ----------

--------------------------------------------------

-------------------------------------------------- ----- ------------

-------------------------

13-MAR-18 08.45.52.057030 AM EUROPE/BERLIN 0

FALSE PLSQL nextExecution

8) enable the job

SQL> exec DBMS_SCHEDULER.ENABLE('TESTJOB');

9) check the table (job started after it was ENABLED)

SQL> select * from testJobTab;

INSERTDATE

-----------------------------

13-MAR-2018 07:47:01

10) check user_scheduler_jobs

There is a difference of 10 days between START_DATE/LAST_START_DATE and NEXT_RUN_DATE. LAST_START_DATE and RUN_COUNT confirms that the job has run.

SQL> select start_date,run_count,last_start_date,next_run_date,enabled,schedule_type,repeat_interval from user_scheduler_jobs where job_name = 'TESTJOB';

START_DATE RUN_COUNT LAST_START_DATE

NEXT_RUN_DATE

ENABL SCHEDULE_TYP REPEAT_INTERVAL

-------------------------------------------------- ----------

--------------------------------------------------

-------------------------------------------------- ----- ------------

-------------------------

13-MAR-18 08.45.52.057030 AM EUROPE/BERLIN 1 13-MAR-18

08.47.01.342411 AM EUROPE/BERLIN 23-MAR-18 12.00.00.000000 AM +00:00

TRUE PLSQL nextExecution

11) disable the job

SQL> exec DBMS_SCHEDULER.DISABLE('TESTJOB');

12) check the table (the job did not run after it was DISABLED)

SQL> select * from testJobTab;

INSERTDATE

-----------------------------

13-MAR-2018 07:47:01

13) check user_scheduler_jobs

No modifications to RUN_COUNT, LAST_START_DATE and NEXT_RUN_DATE.

SQL> select start_date,run_count,last_start_date,next_run_date,enabled,schedule_type,repeat_interval from user_scheduler_jobs where job_name = 'TESTJOB';

START_DATE RUN_COUNT LAST_START_DATE

NEXT_RUN_DATE

ENABL SCHEDULE_TYP REPEAT_INTERVAL

-------------------------------------------------- ----------

--------------------------------------------------

-------------------------------------------------- ----- ------------

-------------------------

13-MAR-18 08.45.52.057030 AM EUROPE/BERLIN 1 13-MAR-18

08.47.01.342411 AM EUROPE/BERLIN 23-MAR-18 12.00.00.000000 AM +00:00

FALSE PLSQL nextExecution

14) enable the job again

SQL> exec DBMS_SCHEDULER.ENABLE('TESTJOB');

15) check the table (notice that the job has run again)

SQL> select * from testJobTab;

INSERTDATE

-----------------------------

13-MAR-2018 07:47:01

13-MAR-2018 07:53:32

16) check user_scheduler_jobs

SQL> select start_date,run_count,last_start_date,next_run_date,enabled,schedule_type,repeat_interval from user_scheduler_jobs where job_name = 'TESTJOB';

START_DATE RUN_COUNT LAST_START_DATE

NEXT_RUN_DATE

ENABL SCHEDULE_TYP REPEAT_INTERVAL

-------------------------------------------------- ----------

--------------------------------------------------

-------------------------------------------------- ----- ------------

-------------------------

13-MAR-18 08.45.52.057030 AM EUROPE/BERLIN 1 13-MAR-18

08.53.32.442546 AM EUROPE/BERLIN 23-MAR-18 12.00.00.000000 AM +00:00

TRUE PLSQL nextExecution

17) drop the job

SQL> exec DBMS_SCHEDULER.drop_job ('TESTJOB');

SQL> drop table testJobTab purge;

SQL> drop function nextExecution;

SQL> drop procedure testJobinsert;



If REPEAT_INTERVAL is used with FREQ and INTERVAL (repeat_interval => 'FREQ=Minutely;INTERVAL=30'), the job does not run when is enabled which is the expected behavior.

原因：
This is caused by

BUG 27697734 - SCHEDULER JOB RUNS IMMEDIATELY AFTER DISABLE/ENABLE WHEN USING PL/SQL-EXPRESSION
处理方法：
The BUG 27697734 - is regressed and tracking in Bug 28174090

Bug 28174090 - IMPDP FAILS WITH ORA-12005 WHEN USING JOBS HAVING PL/SQL REPEAT_INTERVAL

Download the Patch 28174090 from MOS and apply the Patch 28174090 to your current release and platform.