1 2 Previous Next 21 Replies Latest reply: Sep 16, 2010 3:15 PM by hank_nj_usa RSS

    Remove Jobs

    491474
      I used to Toad to DROP all scheduled Jobs. I then query DBA_JOBS and the jobs are still in this table. How do I remove the entries from this table?

      I'm on 9i Release 7.
        • 1. Re: Remove Jobs
          416047
          You need to commit after dropping them.
          • 2. Re: Remove Jobs
            VA
            You can remove a job from the database by issuing a DROP_JOB statement, as in the following:

            BEGIN
            DBMS_SCHEDULER.DROP_JOB (
            job_name => 'my_job1');
            END;
            /

            refer this link:
            http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/schedadmin.htm
            • 3. Re: Remove Jobs
              500897
              Dear Friend,
              Use PL/sql developer 7.0 instead of TOAD... In some cases PL/sql developer 7.0 is much better than TOAD and you might find it useful in your case.


              Valid.
              • 4. Re: Remove Jobs
                491474
                Is there a way to use JOBID to drop the job as oppossed to the jobname?

                I had a look at the link but is doesn't have the syntax.
                • 5. Re: Remove Jobs
                  249425
                  test@MOB92.WORLD> select job, log_user, what from user_jobs;

                  JOB LOG_USER WHAT
                  ---------- ------------------------------ ---------------------------
                  24 TEST p1;

                  test@MOB92.WORLD> exec dbms_job.remove(24)

                  PL/SQL procedure successfully completed.

                  test@MOB92.WORLD> commit;

                  Commit complete.

                  test@MOB92.WORLD> select job, log_user, what from user_jobs;

                  no rows selected

                  Best Regards
                  Krystian Zieja / mob
                  • 6. Re: Remove Jobs
                    491474
                    Unfortunately when I execute exec dbms_job.remove(94)

                    I get an error saying that job 94 is not in the job queue.

                    Just to recap what has happened, I droped the jobs using TOAD but it has not removed them from the DBA_JOBS table.
                    • 7. Re: Remove Jobs
                      249425
                      I assume that this happened to you:
                      mob@MOB92.WORLD> exec dbms_job.remove(24)
                      BEGIN dbms_job.remove(24); END;

                      *
                      ERROR at line 1:
                      ORA-23421: job number 24 is not a job in the job queue
                      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
                      ORA-06512: at "SYS.DBMS_IJOB", line 529
                      ORA-06512: at "SYS.DBMS_JOB", line 171
                      ORA-06512: at line 1

                      This error is caused when you try to execute dbms_job.remove(id) for a job that does not belong to you. You have to be a job owner to execute dbms_job.remove

                      Best Regards
                      Krystian Zieja / mob
                      • 8. Re: Remove Jobs
                        491474
                        Ok. I just logged in as the owner of the jobs and i still got the same error you have just posted about the job not been in the job queue.
                        • 9. Re: Remove Jobs
                          529023
                          SQL>select job,schema_user,priv_user from user_jobs;

                          ORA-23421: job number string is not a job in the job queue
                          Cause: There is no job visible to the caller with the given job number.
                          Action: Choose the number of a job visible to the caller.
                          • 10. Re: Remove Jobs
                            491474
                            Ok. I have executed the query select job,schema_user,priv_user from user_jobs;

                            in the schema that contained the jobs but no data is returned however, DBA_JOBS still has the entries.
                            • 11. Re: Remove Jobs
                              529023
                              SQL> conn sys as sysdba
                              Enter password:
                              Connected.
                              SQL> select count(*) from dba_jobs;

                              COUNT(*)
                              ----------
                              3

                              SQL> conn oracle/oracle
                              Connected.
                              SQL> select count(*) from user_jobs;

                              COUNT(*)
                              ----------
                              0
                              • 12. Re: Remove Jobs
                                491474
                                Ok, both SYS & rptuser show a total of 14.
                                • 13. Re: Remove Jobs
                                  RnR
                                  As you have probably realized dbms_scheduler refers to Oracle Scheduler jobs which are different to dbms_job jobs (dbms_scheduler is the 10g replacement for dbms_job and should be used in 10g or higher) .

                                  Apart from summarizing what was said above (only the owner of a dbms_job job can dbms_job.remove it and you need to commit after remving) the only other thing I can think of that might help is that querying the underlying table may show more clearly what is happening .

                                  Selecting from sys.job$ will show you the data in the underlying table that dbms_job is seeing (powner is the real owner of the jobs). Once you have figured out the job number and owner from there all you should have to do is login as that user , do dbms_job.remove and commit . Also selecting from user_jobs as a particular user should show only jobs owned by that user.

                                  Hope this helps,
                                  Ravi.
                                  • 14. Re: Remove Jobs
                                    180026
                                    Hi Steve,

                                    Have you tried using dbms_ijob?

                                    logon as sysdba and use:
                                    to drop all users jobs

                                    execute dbms_ijob.DROP_USER_JOBS('myuser');

                                    or to drop a specific job

                                    execute dbms_ijob.remove(94);

                                    cheers,
                                    john
                                    1 2 Previous Next