Wrong focus
Peter Nosko, October 21, 2017 - 5:54 pm UTC
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.
The solution was not obvious
Peter Nosko, October 23, 2017 - 6:40 pm UTC
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.
One final point
Peter Nosko, October 24, 2017 - 3:20 pm UTC
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.
The example works but ...
Pedro, June 07, 2018 - 8:12 pm UTC
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
Table T not populated
Peter, June 12, 2018 - 1:19 pm UTC
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>
REPEAT_INTERVAL 'freq=daily; bysecond=0;' is the difference
Peter, June 13, 2018 - 2:04 pm UTC
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.
My bad malforming the problem
Peter, June 14, 2018 - 8:53 pm UTC
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 ?
One word answer
Peter, June 15, 2018 - 2:33 pm UTC
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
:-)