5 Replies Latest reply: Jan 2, 2013 11:55 PM by 973492 RSS

    unable to drop a broken job

    973492
      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
          >
          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
            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
              >
              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
                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
                  Thanks all...working fine....