OraFAQ Forum: SQL & PL/SQL » schedule is not running

Home » SQL & PL/SQL » SQL & PL/SQL » schedule is not running (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

schedule is not running [message #689901]

Tue, 09 July 2024 02:32

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

Report message to a moderator

Re: schedule is not running [message #689902 is a reply to message #689901]

Tue, 09 July 2024 03:28

John Watson
Messages: 8960
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?

Report message to a moderator

Re: schedule is not running [message #689903 is a reply to message #689901]

Tue, 09 July 2024 03:33

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: schedule is not running [message #689904 is a reply to message #689902]

Tue, 09 July 2024 06:42

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

Report message to a moderator

Previous Topic:	Left Outer Join Error (merged) (duplicate deleted by bb)
Next Topic:	specific text from the string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 08:10:17 CST 2024