2

Our Oracle Scheduler isn't working in several of our environments but does work in some. In the environments where it is working, the following query returns 1 row

select program from v$session where osuser = 'oracle' and program like '%(CJQ0)%';

[email protected] (CJQ0)

In the environments where it is not working, no rows are returned.

This is the test that I performed to see if the Scheduler was running. It should instantly fire off the job.

CREATE TABLE MySchema.TEMP_DBMS_JOB (
  A NUMBER(*, 0))

DECLARE 
l_jobid int;
BEGIN
    dbms_job.submit(job => l_jobid, what => 'begin insert into MySchema.temp_dbms_job (a) values (13); commit; end;');
    dbms_output.put_line(l_jobid);
    COMMIT;
END;

What can the DBA do to get the Scheduler running? Does the "CJQ0" session query help you identify what might be the underlying source of the problem? The DBA sees no issues with the configuration of the scheduler.

We also tried DBMS_SCHEDULER.create_job to submit the jobs instead of the deprecated dbms_job.submit and had the exact same outcome in the same environments.

Improve this question
3
  • Compare the the init parameter job_queue_processes with # of jobs running in the system (dba_scheduler_running_jobs) per instance. If it's maxed, you can't run a job until other jobs complete. Sometimes however Oracle gets them into a funky status and thinks they are unavailable when that's not true. In this case, in some versions we've had success setting job_queue_processes to 0 and then back to what it was originally. If that doesn't work, try raising job_queue_processes a bit over its current value.
    – Paul W
    Aug 14 at 22:42
  • job_queue_processes = 440, no running jobs. It doesn't work for any users. The last time we tried in any env was a month ago, when it worked in all envs. I am not aware of any DB patches that were installed but I'm not a DBA.
    – Ivan
    Aug 14 at 23:23
  • Definitely try setting it to 0 and then back again. And if that doesn't work, it might be time to bounce the instance. And if that doesn't work, an SR with Oracle. But an instance bounce usually fixes most things like this.
    – Paul W
    Aug 15 at 1:57

2 Answers 2

Reset to default
1

Troubleshooting questions and answers:

  1. What is parameter JOB_QUEUE_PROCESSES set to? 440
  2. Do jobs run for any users? No
  3. When did jobs last run? A month ago
  4. Were any patches installed since then? Not that I'm aware of.
  5. Have you tried turning it on and off again? (Set job_queue_processes to 0 and then back to the original value.) (todo)
  6. Are there any rows in DBA_SCHEDULER_JOBS or DBA_SCHEDULER_JOB_RUN_DETAILS? (todo)
  7. When you run DBA_SCHEDULER.CREATE_JOB, are there any errors thrown? (todo)
  8. If there are rows in DBA_SCHEDULER_JOBS, can you paste some sample rows? (Which may be difficult because there are dozens of columns in the table.) (todo)

I created a Wiki answer for this question since I assume there will be a lot of back and forth, and this checklist will be easier to read through than a million comments. I'm pretty sure I've literally seen a checklist like this on support.oracle.com, but I can't find it anymore.

Improve this answer
1
  • 1
    Sorry, it was difficult to get answers to all of your questions because I did not have rights to do them myself and i was working on a work around to run our process without using the scheduler. In the end, the issue was resolved by restarting the database container. Thanks for your help.
    – Ivan
    Aug 16 at 16:25
1

The problem was resolve by restarting the database 'container'.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.