Oracle Job Scheduler cannot stop running Job

Question

We have an issue in one of our production databases which I cannot find a solution for. One of our Jobs is stuck in the RUNNING state and attempting to stop it produces a ORA-27366: job "NameSpace"."Some_Job" is not running. Querying the DBA_SCHEDULER_JOBS table have the job listed as RUNNING, but what's odd is that The Next_Run_Date is greater than the Last_Start_Date by about 10 minutes.

Attempting to stop the job by using:

-- Stop Job
BEGIN
    DBMS_SCHEDULER.STOP_JOB(job_name => 'NameSpace.SomeJob', force => TRUE);
    DBMS_OUTPUT.PUT_LINE('Stopped Job - SUCCESS');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Stopping of Job FAILED - ' ||  SQLERRM);

Gave me the following:

Stopping of Job - FAILED - ORA-27366: job "NameSpace"."SomeJob" is not running

My question is, how do I really stop the job?

Maybe it is `job_name => "NameSpace"."SomeJob"`? Difficult to help you if you conceal the names and thus most likely hide the problem. — Wernfried Domscheit, Jun 25 '18 at 15:05
The `Job_Name` is correct, it shouldn't matter if it's concealed to understand the problem. This is a Job that was built to run every 2 minutes. The `Next_Run_Date` should be 2 minutes after `Last_Start_Date`. — Jimenemex, Jun 25 '18 at 15:09
What's wrong with images? The `DBA_SCHEDULER_JOB_LOG` is empty. If I recall correctly, doesn't that table only get populated after the job is complete. I'm unsure though. — Jimenemex, Jun 25 '18 at 15:19
Please don't paste images, use formatted text. And have a look at `DBA_SCHEDULER_JOB_LOG` and `DBA_SCHEDULER_JOB_RUN_DETAILS` — Wernfried Domscheit, Jun 25 '18 at 15:21

score -1 · Answer 1 · answered Jun 25 '18 at 15:22

-1

Maybe try this one:

DECLARE

    CURSOR Jobs IS
    SELECT OWNER, JOB_NAME, LAST_START_DATE
    FROM USER_SCHEDULER_JOBS
    WHERE JOB_NAME = 'whatever you try to hide from us'
        AND STATE = 'RUNNING';

BEGIN

    FOR aJob IN Jobs LOOP
    BEGIN
        DBMS_SCHEDULER.STOP_JOB(aJob.OWNER||'.'||aJob.JOB_NAME, FORCE => TRUE);
    EXCEPTION
         WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Stopping of Job "'||aJob.OWNER||'.'||aJob.JOB_NAME||'" FAILED - ' ||  SQLERRM);
    end;
    END LOOP;
END;

View DBA_SCHEDULER_RUNNING_JOBS should also work

answered Jun 25 '18 at 15:22

Wernfried Domscheit

54,457
9
76
110

I'm sorry, but I fail to see how this is only slightly different then what I have tried. Could you please elaborate on what's different between the two? – Jimenemex Jun 25 '18 at 15:25
It ensures that you don't make a typo between query and STOP_JOB. It ensures that the job did not finish in between execution of query and execute STOB_JOB. – Wernfried Domscheit Jun 25 '18 at 17:35

Oracle Job Scheduler cannot stop running Job

1 Answers1