Few days ago we noticed one of our jobs in the database was not executing regularly. Job was defined through dbms_scheduler, and should be running each day. However it did not. Looking on the Enterprise Manager we could see that the job was still running from it’s last run (few days ago). That would explain why it did not start subsequently. Now the question is why was it still stated as running.
Closer examination showed that the state of the job really is running, but there is no session associated with the job and that if we try to stop the execution of a job manually, we get an ORA-27478: job is running.
SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB' AND OWNER = 'JOB_OWNER'; STATE JOB_NAME OWNER --------------- -------------------- --------------- RUNNING MY_JOB JOB_OWNER
A search on metalink however revealed a note 422134.1 stating a possible scheduler problem that may occur on Oracle versions 10.1.0.0 – 10.2.0.3. Solution: drop the job and recreate it later. 🙁
So we tried that, but cjq0 stopped us from doing that with lock it had on the job making it impossible to stop our not-running job. Another metalink note (330725.1) mentions exactly this situation and adds another step to our solution, killing the cjq0 process. Here is the exact procedure: