27 Deciding Not to Run Again
I think I threw you off tangent by even mentioning history. I'm not interested in the job history; our jobs maintain an application table for that purpose. My bad. Let me try again.
I have a job scheduled to run weekly. It has been running fine for months. All of a sudden, I have a need to skip a week. Or maybe I just want to tweak the time.
JOB_NAME REPEAT_INTERVAL START_DATE LAST_START_DATE NEXT_RUN_DATE ------------------------------ ------------------------- -------------------- -------------------- -------------------- ADJ_CUTOFF_SWEEP FREQ=WEEKLY;INTERVAL=1 2017-10-12 02:00:00 2017-10-19 02:00:01 2017-10-26 02:00:00
I want the job to now run at 3AM instead. Is there a way to change that NEXT_RUN_DATE without dropping and recreating the job?
October 22, 2017 - 9:26 am UTC
In that case, its trivial - you just change set an attribute
SQL> create table t ( x timestamp); Table created. SQL> create or replace procedure logger is 2 begin 3 insert into t values (localtimestamp); 4 commit; 5 end; 6 / Procedure created. SQL> begin 2 dbms_scheduler.create_job ( 3 job_name => 'my_job', 4 job_type => 'plsql_block', 5 job_action => 'begin logger; end;', 6 start_date => systimestamp, 7 repeat_interval => 'freq=minutely; bysecond=0;', 8 enabled => true); 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> select * from t; X ------------------------------------------------------------ 22-OCT-17 05.16.00.364000 PM 22-OCT-17 05.15.00.510000 PM SQL> begin 2 dbms_scheduler.set_attribute ( 3 name => 'my_job', 4 attribute => 'start_date', 5 value => sysdate+300/86400); 6 end; 7 / PL/SQL procedure successfully completed. SQL> select * from t; X ------------------------------------- 22-OCT-17 05.16.00.364000 PM 22-OCT-17 05.17.00.382000 PM 22-OCT-17 05.23.00.061000 PM 22-OCT-17 05.24.00.202000 PM 22-OCT-17 05.25.00.396000 PM 22-OCT-17 05.15.00.510000 PM 6 rows selected.
Connor, thank you for making me look a little harder at a solution I had tried, but didn't give enough time. The Oracle documentation on setting the START_TIME says "The original date and time on which this job started or is scheduled to start." The term "original" did not give me a warm fuzzy that altering it after the job had started running on schedule would have any affect, so I did not notice that altering it also causes the NEXT_RUN_DATE to be updated. Thanks again.
October 24, 2017 - 1:06 am UTC
Glad we could help.
When the START_DATE is modified, the NEXT_RUN_DATE is only updated if/when the job is/becomes ENABLED. This is probably why I thought my earlier tests were not working. I had disabled the job while deciding when to reschedule the next execution.
Connor,
Your last example works.
How about instead of the start_date being larger that the frequency we make the start_date smaller than frequency:
create table t ( x timestamp);
create or replace procedure logger is
begin
insert into t values (localtimestamp);
commit;
end;
/
TRUNCATE TABLE T;
begin
dbms_scheduler.create_job (
job_name => 'my_job',
job_type => 'plsql_block',
job_action => 'begin logger; end;',
start_date => systimestamp,
-- repeat_interval => 'freq=minutely; bysecond=0;',
repeat_interval => 'freq=daily; bysecond=0;',
enabled => true);
end;
/
begin
dbms_scheduler.set_attribute (
name => 'my_job',
attribute => 'start_date',
value => sysdate+300/86400);
end;
/
... and nothing happens.
How does one make the job to execute right away and than stick to the predefined frequency schedule (just like your example did)
Thank you,
Pedro
June 08, 2018 - 7:07 am UTC
begin dbms_scheduler.set_attribute ( name => 'my_job', attribute => 'start_date', value => sysdate+300/86400); end; / ... and nothing happens.
I would expect nothing to happen for 5 minutes...if you want it to start now, then start_date would be sysdate
Connor,
Thank you for the quick response and apologize for the delay.
I executed:
begin
dbms_scheduler.set_attribute (
name => 'my_job',
attribute => 'start_date',
value => sysdate);
end;
/
the start_date and next_run_date in dba_scheduler_jobs changed, last_start_date remains NULL, procedure logger never executed, table t is empty.
Where do we go from here?
Thank you,
Peter
June 13, 2018 - 2:12 am UTC
Hmmm... can I get a top to bottom test case ? I'm not seeing that unless I fiddle with enabled, eg
SQL> create table t ( x timestamp); Table created. SQL> SQL> create or replace procedure logger is 2 begin 3 insert into t values (localtimestamp); 4 commit; 5 end; 6 / Procedure created. SQL> SQL> begin 2 dbms_scheduler.create_job ( 3 job_name => 'my_job', 4 job_type => 'plsql_block', 5 job_action => 'begin logger; end;', 6 start_date => systimestamp, 7 repeat_interval => 'freq=minutely; bysecond=0;', 8 enabled => false); 9 end; 10 / PL/SQL procedure successfully completed. -- -- nothing because not enabled -- SQL> SQL> select * from t; no rows selected SQL> SQL> begin 2 dbms_scheduler.set_attribute ( 3 name => 'my_job', 4 attribute => 'start_date', 5 value => sysdate); 6 end; 7 / PL/SQL procedure successfully completed. -- -- still nothing because not enabled -- SQL> SQL> select * from t; no rows selected SQL> select * from t; no rows selected -- -- I enable -- SQL> exec dbms_scheduler.enable('my_job'); PL/SQL procedure successfully completed. SQL> select sysdate from dual; SYSDATE ------------------- 13/06/2018 10:10:02 1 row selected. -- -- Nothing (briefly) whilst the scheduler kicks into gear -- SQL> select * from t; no rows selected -- -- and then we're good -- SQL> / X --------------------------------------------------------------------------- 13-JUN-18 10.10.02.708000 AM 1 row selected. SQL> Connor,
Underneath is the top to bottom test case. The only difference between yours and mine is in dbms_scheduler.create_job, you have repeat_interval => 'freq=minutely; bysecond=0;', and I have repeat_interval => 'freq=daily; bysecond=0;'. With repeat_interval => 'freq=daily; bysecond=0;' the test case does not populate table t after executing dbms_scheduler.set_attribute value => SYSDATE.
create table t ( x timestamp);
create or replace procedure logger is
begin
insert into t values (localtimestamp);
commit;
end;
/
TRUNCATE TABLE T;
begin
dbms_scheduler.create_job (
job_name => 'my_job',
job_type => 'plsql_block',
job_action => 'begin logger; end;',
start_date => systimestamp,
--Connor-- repeat_interval => 'freq=minutely; bysecond=0;',
repeat_interval => 'freq=daily; bysecond=0;',
enabled => true);
end;
/
begin
dbms_scheduler.set_attribute (
name => 'my_job',
attribute => 'start_date',
value => sysdate);
end;
/
Thank you,
Peter
June 14, 2018 - 2:18 am UTC
It is your "bysecond" parameter. We're going to run this on second 00, and if not, then we'll wait until tomorrow and do it then.
Example 1, submitted at any old time
SQL> create table t ( x timestamp); Table created. SQL> create or replace procedure logger is 2 begin 3 insert into t values (localtimestamp); 4 commit; 5 end; 6 / Procedure created. SQL> TRUNCATE TABLE T; Table truncated. SQL> begin 2 3 dbms_scheduler.create_job ( 4 job_name => 'my_job', 5 job_type => 'plsql_block', 6 job_action => 'begin logger; end;', 7 start_date => systimestamp, 8 repeat_interval => 'freq=daily; bysecond=0;', 9 enabled => true); 10 end; 11 / PL/SQL procedure successfully completed. SQL> select sysdate, count(*), max(x) from t; SYSDATE COUNT(*) MAX(X) ------------------- ---------- ---------------------------------- 14/06/2018 10:15:52 0 SQL> SQL> / SYSDATE COUNT(*) MAX(X) ------------------- ---------- ---------------------------------- 14/06/2018 10:15:56 0 SQL> / SYSDATE COUNT(*) MAX(X) ------------------- ---------- ---------------------------------- 14/06/2018 10:15:59 0 SQL> / SYSDATE COUNT(*) MAX(X) ------------------- ---------- ---------------------------------- 14/06/2018 10:15:59 0 SQL> / SYSDATE COUNT(*) MAX(X) ------------------- ---------- ---------------------------------- 14/06/2018 10:16:03 0 SQL> / SYSDATE COUNT(*) MAX(X) ------------------- ---------- ---------------------------------- 14/06/2018 10:16:04 0
Example 2 - submitted on second 00
SQL> create table t ( x timestamp); Table created. SQL> create or replace procedure logger is 2 begin 3 insert into t values (localtimestamp); 4 commit; 5 end; 6 / Procedure created. SQL> TRUNCATE TABLE T; Table truncated. SQL> begin 2 while to_char(sysdate,'SS')!= '00' loop --| 3 dbms_lock.sleep(0.1); --| 4 end loop; --| 5 dbms_scheduler.create_job ( 6 job_name => 'my_job', 7 job_type => 'plsql_block', 8 job_action => 'begin logger; end;', 9 start_date => systimestamp, 10 repeat_interval => 'freq=daily; bysecond=0;', 11 enabled => true); 12 end; 13 / PL/SQL procedure successfully completed. SQL> select sysdate, count(*), max(x) from t; SYSDATE COUNT(*) MAX(X) ------------------- ---------- ------------------------------------- 14/06/2018 10:17:00 0 SQL> / SYSDATE COUNT(*) MAX(X) ------------------- ---------- ------------------------------------- 14/06/2018 10:17:03 1 14-JUN-18 10.17.00.133000 AM
Typically I'd imagine you would want to be more specific on your interval, ie, "I want to run to at 1pm each day" etc.
Connor,
Thank you very much for explaining in details why the repeat_interval => 'freq=daily; bysecond=0;' won't work. I apologize but I malformed my problem. Underneath is the sample case, again the only difference between yours and mine is the repeat_interval in dbms_scheduler.create_job. The job will be executed by the scheduler on MON,TUE,WED,THU,FRI,SAT; at 1:00. How could one execute the job 1 time now (SYSDATE) without affecting the above schedule?
create table t ( x timestamp);
create or replace procedure logger is
begin
insert into t values (localtimestamp);
commit;
end;
/
TRUNCATE TABLE T;
begin
dbms_scheduler.create_job (
job_name => 'my_job',
job_type => 'plsql_block',
job_action => 'begin logger; end;',
start_date => systimestamp,
-- repeat_interval => 'freq=minutely; bysecond=0;',
-- repeat_interval => 'freq=daily; bysecond=0;',
repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1; BYMINUTE=0',
enabled => true);
end;
/
Thank you,
Peter
June 15, 2018 - 1:13 am UTC
dbms_scheduler.run_job ?
Yes, yes and yes... it works. Not in vain you manage asktom.com where men go when there is no hope. Apologize for the misguided test case and thank you very much.
June 16, 2018 - 1:20 am UTC
:-)
kitamuramaugh1943.blogspot.com
Source: https://asktom.oracle.com/pls/apex/asktom.search?tag=change-a-dbms-scheduler-jobs-next-run-date
0 Response to "27 Deciding Not to Run Again"
Post a Comment