This discussion is archived
5 Replies Latest reply: Jan 2, 2013 9:55 PM by 973492 RSS

unable to drop a broken job

973492 Newbie
Currently Being Moderated
Hi Friends,

i want to drop a broken job but it is taking long time why?

i am using below syntax:

begin
dbms_job.broken(job=> 345,FALSE);
commit;
end;
/


Thanx.
  • 1. Re: unable to drop a broken job
    rp0428 Guru
    Currently Being Moderated
    >
    i want to drop a broken job but it is taking long time why?

    i am using below syntax:

    begin
    dbms_job.broken(job=> 345,FALSE);
    commit;
    end;
    /
    >
    Why do you think the job is broken? You are setting broken to FALSE.
    Example
    BEGIN
       DBMS_JOB.REMOVE(14144);
       COMMIT;
    END; 
    See the BROKEN procedure in the doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_job.htm#i1000390
    >
    If you set job as broken while it is running, Oracle resets the job's status to normal after the job completes. Therefore, only execute this procedure for jobs that are not running.
  • 2. Re: unable to drop a broken job
    973492 Newbie
    Currently Being Moderated
    In user_jobs i am able to see job status is broken. So the job is not picked up, so i am trying to drop it up and planning to create new one. Still it is not allowing me to drop as well as unable to mark the broken status as 'N'.
  • 3. Re: unable to drop a broken job
    rp0428 Guru
    Currently Being Moderated
    >
    In user_jobs i am able to see job status is broken. So the job is not picked up, so i am trying to drop it up and planning to create new one. Still it is not allowing me to drop as well as unable to mark the broken status as 'N'.
    >
    And you think BROKEN='N' means the job is broken?

    Like I showed you, you need to remove the job; it is already broken.
  • 4. Re: unable to drop a broken job
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    you can drop the job and recreate it. To drop the job :-
    BEGIN
      DBMS_SCHEDULER.drop_job (job_name => 'job_name);
    END;
    To create a job
    BEGIN
      DBMS_SCHEDULER.create_job (
        job_name        => 'job_name',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN your_procedure; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
        end_date        => NULL,
        enabled         => TRUE,
        comments        => 'any_comment_you_want.');
    END;
    /
  • 5. Re: unable to drop a broken job
    973492 Newbie
    Currently Being Moderated
    Thanks all...working fine....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points