9 Replies Latest reply: Jun 13, 2014 5:43 AM by 985871 RSS

    oracle dbms_job.remove

    985871

      Hi Gurus ,

       

      I need your help in re instating a job which i accidently removed using the below command .

      oracle 10g version .

       

      dbms_job.remove(506052);

      commit;

       

      Can you please help me use the same job number and schedule it at 02:00 am everyday .

       

      Thanks

       

      Regards,

      DBApss

        • 1. Re: oracle dbms_job.remove
          sb92075

          985871 wrote:

           

          Hi Gurus ,

           

          I need your help in re instating a job which i accidently removed using the below command .

          oracle 10g version .

           

          dbms_job.remove(506052);

          commit;

           

          Can you please help me use the same job number and schedule it at 02:00 am everyday .

           

          Thanks

           

          Regards,

          >dbms_job.remove(506052);

          >commit;

           

          does not appear to me to be an accident. It appears the removal was deliberative & purposeful.

           

          we don't know what this job is supposed to do.

          • 2. Re: oracle dbms_job.remove
            985871

            Hi ,

            Yes i did it to stop the job from re running as i am already submitted this job to run mannually . but it removed it completely .

            This job is presently running .

            Can you please help .

             

            Regards,

            DBApps

            • 3. Re: oracle dbms_job.remove
              JustinCave

              You can't (realistically) use the same job number.

               

              You can resubmit the job, using dbms_job.submit.  You'll need to know all the parameters to pass though (presumably the schedule and the code the job is supposed to run).

               

              Justin

              • 4. Re: oracle dbms_job.remove
                rp0428
                I need your help in re instating a job which i accidently removed using the below command .

                oracle 10g version .

                 

                dbms_job.remove(506052);

                commit;

                Why do you need help? Just check out the DDL from your version control system and execute it.

                Can you please help me use the same job number and schedule it at 02:00 am everyday .

                No - Job numbers are assigned by Oracle. When you create a job it will use the next available job number.

                 

                Why do you care what the job number is?

                • 5. Re: oracle dbms_job.remove
                  985871

                  Hi ,

                  Thanks for your suggestions . I have executed the below code but it dosent shows up under dba_jobs except the job number.

                   

                  SQL> var jobno number;
                  SQL> exec dbms_job.submit(:jobno,what=>'DAILYAGG.LD_DAY_TEST;',next_date=>trunc(sysdate+1),interval=>'trunc(sysdate+1)+2/24');

                  PL/SQL procedure successfully completed.

                  SQL> print :jobno

                       JOBNO
                  ----------
                      507772

                  SQL> commit;

                  • 6. Re: oracle dbms_job.remove
                    JustinCave

                    Correct.  Once you remove a job, Oracle won't re-use the job number if you submit a new job.  Even if the new job is doing the same thing that the old job was doing.  That's why you've been told now multiple times that you can't reuse the job number.

                     

                    Justin

                    • 7. Re: oracle dbms_job.remove
                      985871

                      Thanks justin ,

                      But my query is when will it start showing under dba_jobs with the date and next interval as it is blank currently for this job.

                       

                      DBApps

                      • 8. Re: oracle dbms_job.remove
                        JustinCave

                        OK, now I'm confused as to what " it dosent shows up under dba_jobs except the job number." means.

                         

                        Have you committed the change?  Which column(s) specifically in dba_jobs are empty that you would expect to be populated?  There are, off the top of my head, at least 3 "date columns" in the view-- last_date, this_date, and next_date of which last_date and this_date would be expected to be NULL once you've submitted the job until the job is started for the first time.

                         

                        Justin

                        • 9. Re: oracle dbms_job.remove
                          985871


                          Justin ,

                           

                          Thanks alot ! . Yes i was expecting that the last_date,next_date and so on will show up immediately and as you replied it will show up only when job runs the first time.

                           

                          Regards,

                          DBApps