Home » SQL & PL/SQL » SQL & PL/SQL » schedule is not running (Oracle 10g)
schedule is not running [message #689901] Tue, 09 July 2024 02:32 Go to next message
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 messageGo to next message
John Watson
Messages: 8939
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 messageGo to next message
cookiemonster
Messages: 13926
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.
Re: schedule is not running [message #689904 is a reply to message #689902] Tue, 09 July 2024 06:42 Go to previous message
emadnabil
Messages: 179
Registered: August 2007
Senior Member
Many thanks for your support

I checked on it
and the problem was missing privilege on "Manage schedule"

many thanks Mr. john

Previous Topic: Left Outer Join Error (merged) (duplicate deleted by bb)
Next Topic: Row generator
Goto Forum:
  


Current Time: Mon Jul 22 22:26:43 CDT 2024