标题: Scheduler Job Runs Immediately After Enable When Using PL/SQL-expression [打印本页] 作者: 刘泽宇 时间: 2024-6-2 17:26 标题: 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);
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';
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';
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';
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';
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.