schedule is not running [message #689901] |
Tue, 09 July 2024 02:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
Good morning
I have created a job using the below code
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'run_daily_proc',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN update_emp_consumption; END;',
schedule_name => 'daily_proc_schedule',
enabled => TRUE
);
END;
and created a schedule using the below code
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'daily_proc_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY' -- Runs daily
);
END;
the job is running manual successfully
but it is not running through the schedule
kindly support in this issue
|
|
|
Re: schedule is not running [message #689902 is a reply to message #689901] |
Tue, 09 July 2024 03:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It works for me running it minutely:orclz> create procedure update_emp_consumption as begin
2 null;
3 end;
4 /
Procedure created.
orclz> BEGIN
2 DBMS_SCHEDULER.CREATE_SCHEDULE(
3 schedule_name => 'daily_proc_schedule',
4 start_date => SYSTIMESTAMP,
5 repeat_interval => 'FREQ=MINUTELY'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
orclz> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(
3 job_name => 'run_daily_proc',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN update_emp_consumption; END;',
6 schedule_name => 'daily_proc_schedule',
7 enabled => TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
orclz> select job_name,enabled,state,auto_drop from user_scheduler_jobs;
JOB_NAME ENABL STATE AUTO_
------------------------------ ----- -------------------- -----
RUN_DAILY_PROC TRUE SCHEDULED TRUE
orclz> select job_name,enabled,state,auto_drop,last_start_date,next_run_date from user_scheduler_jobs;
JOB_NAME ENABL STATE AUTO_ LAST_START_DATE
------------------------------ ----- -------------------- ----- -----------------------------------
NEXT_RUN_DATE
-----------------------------------
RUN_DAILY_PROC TRUE SCHEDULED TRUE
09-JUL-24 09.23.24.976000 +01:00
orclz> /
JOB_NAME ENABL STATE AUTO_ LAST_START_DATE NEXT_RUN_DATE
------------------------------ ----- -------------------- ----- ----------------------------------- -----------------------------------
RUN_DAILY_PROC TRUE SCHEDULED TRUE 09-JUL-24 09.23.24.976000 +01:00
orclz> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUL-24 09.23.45.084000 +01:00
orclz> select job_name,enabled,state,auto_drop,last_start_date,next_run_date from user_scheduler_jobs;
JOB_NAME ENABL STATE AUTO_ LAST_START_DATE NEXT_RUN_DATE
------------------------------ ----- -------------------- ----- ----------------------------------- -----------------------------------
RUN_DAILY_PROC TRUE SCHEDULED TRUE 09-JUL-24 09.23.25.075000 +01:00 09-JUL-24 09.24.24.090000 +01:00
orclz> select job_name,enabled,state,auto_drop,last_start_date,next_run_date from user_scheduler_jobs;
JOB_NAME ENABL STATE AUTO_ LAST_START_DATE NEXT_RUN_DATE
------------------------------ ----- -------------------- ----- ----------------------------------- -----------------------------------
RUN_DAILY_PROC TRUE SCHEDULED TRUE 09-JUL-24 09.24.24.287000 +01:00 09-JUL-24 09.25.24.287000 +01:00
orclz> That was on release 19.17, but I would thing the release is not important. If you run my test above, what do you get?
|
|
|
Re: schedule is not running [message #689903 is a reply to message #689901] |
Tue, 09 July 2024 03:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Check the JOB_QUEUE_PROCESSES system parameter is greater than 0.
Then look in user_scheduler_jobs - it's got all the data about the state of all scheduler jobs - last run time, next run time, error count, status, etc.
|
|
|
|