This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Sep 16, 2010 1:15 PM by hank_nj_usa RSS

Remove Jobs

491474 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    You need to commit after dropping them.
  • 2. Re: Remove Jobs
    430730 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Ok, both SYS & rptuser show a total of 14.
  • 13. Re: Remove Jobs
    RnR Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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