1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-27366:how to stop a JOB which is running.,

Discussion in 'SQL PL/SQL' started by Vicky, Feb 26, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi.,

    I've created a job and it's working fine.,

    But when i try to stop it I'm getting the error as below:

    Error report:
    ORA-27366: job "TEST_USER.JOB_DEF" is not running
    ORA-06512: at "SYS.DBMS_ISCHED", line 164
    ORA-06512: at "SYS.DBMS_SCHEDULER", line 483
    ORA-06512: at line 1
    27366. 00000 - "job \"%s.%s\" is not running"
    *Cause: An attempt was made to stop a job that was not running.
    *Action: Verify the status of the job. If the job is running but this
    message is still being returned, contact Oracle support.

    And the query below also not fetching the Job which is running.,

    SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;

    Is it a problem related to Job ?!
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Vicky .

    is the job showing in the result SELECT * FROM USER_SCHEDULER_RUNNING_JOBS?

    Could you please mention how you are trying to stop the job . ?
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I tried the block below to stop the job:

    BEGIN dbms_scheduler.stop_job('JOB_DEF'); END;

    And the below is NOT fetching the Job which is running currently.,

    SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;

    But I can see the changes in the table, which shows that my job is running.,
     
  4. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    As per my understanding from above reply . you are not trying to stop the running job . you are trying to stop the schedule .

    STOP_JOB Procedure stops currently running jobs. As SELECT * FROM USER_SCHEDULER_RUNNING_JOBS not returning data means currently it is not running might it start and completed normal and the changes are reflecting in your table .

    I believe if you disable the schedule it will not run .I could find below link for enable/disable the schedule

    https://oraclespin.wordpress.com/2008/11/26/how-to-enabledisable-a-scheduled-job/

    This might help you
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    But I can still see the values keep on changing.,

    And it is the Job name only., I will share U code, which I used for creating job.,

    BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => 'job_def',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN UPDEMP(); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'freq=MINUTELY;bysecond=0;',
    end_date => NULL,
    enabled => TRUE,
    comments => 'Job defined entirely by the CREATE JOB procedure.');
    END;
    /

    create or replace procedure UPDEMP is
    begin
    update employees
    set salary = salary+100
    where emp_id=50;
    commit;
    end;

    /
     
  6. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Vicky likes this.
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes, Thanks RG Hegde. I can disable it. But even after changing the repeat_interval and enabling, i can't get the job_name in the result set of the below query:

    SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;

    Or is der any other query to check the jobs which are running currently in the schema.,?
     
  8. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Vicky ,

    USER_SCHEDULER_RUNNING_JOBS displays information about the running Scheduler jobs owned by the current user.

    If that job is not running it will not show you the job details .

    You can do one thing . Try to submit that job manually and check that . the job might complete in within few seconds . in that case before you try to check in
    USER_SCHEDULER_RUNNING_JOBS it will complete.
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Is submitting a Job like restarting the already running Job.,

    or it's like setting attributes to the Job.,?

    Cud U tel me what kind of impact will it make on the existing Job.,/!
     
  10. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    I believe once it is disabled it will not run. The schedule will be stopped.
     
  11. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes, Hegde., You're correct., the values aren't changing after disabling the Job.,

    I just asked what DBMS_JOB.SUBMIT will do?!

    I read about create,enable,disable,drop on Job., But I dunno how DBMS_JOB.SUBMIT works., Cud U expln me Submit with examples/links necessary,./