This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Nov 3, 2011 11:32 PM by BillyVerreynne RSS

How to drop a job?

834783 Newbie
Currently Being Moderated
I am using 8.1.7.4

These commands do not remove the job 61...

SELECT * FROM dba_jobs_running;

SELECT JOB FROM DBA_JOBS WHERE JOB=61;

BEGIN
EXECUTE DBMS_JOB.REMOVE(61);
COMMIT;
END;

SELECT JOB FROM DBA_JOBS WHERE JOB=61;
  • 1. Re: How to drop a job?
    mseberg Guru
    Currently Being Moderated
    Hello;

    Your command is correct :

    EXECUTE DBMS_JOB.REMOVE(jobno);

    Restrictions:

    You can remove currently executing jobs from the job queue.

    You can remove only jobs you own.

    Something like this might allow you to drop it.

    DBMS_JOB.CHANGE(61, null, null, 'SYSDATE + 3');

    See A76956-01 Oracle8i Administrator's Guide for more information.

    Best Regards

    mseberg
  • 2. Re: How to drop a job?
    damorgan Oracle ACE Director
    Currently Being Moderated
    Working demos of DBMS_JOB functionality can be found here:
    http://www.morganslibrary.org/library.html
    under DBMS_JOB.

    But keep in mind DBMS_JOB has been obsolete for many versions ... move to 11gR2 and DBMS_SCHEDULER as soon as you can.
  • 3. Re: How to drop a job?
    P.Forstmann Guru
    Currently Being Moderated
    Try first to retrieve SID and SERIAL for running job and to kill job session:
    select sid, serial# 
    from v$session
    where sid = (select sid from dba_jobs_running where job=61);
    Kill the job session:
    alter system kill session '<sid>,<serial#>';
    And then try to remove job.
  • 4. Re: How to drop a job?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Actually do it the other way around. It is safer.

    If you kill a job, the job process fails and the Job Queue Manager will detect it - and restart the job.

    It can do that restart before you have successfully deleted that job from the job queue.

    Thus is is better to first delete the job (ensuring that it will not be restarted) and then kill the session running that job.
  • 5. Re: How to drop a job?
    161735 Explorer
    Currently Being Moderated
    As said you have to be the owner of the job and then you could run something like this

    select 'exec dbms_job.broken(job => '||a.job||',broken => true);','alter system kill session '''||a.sid||','||a.SERIAL#||''';',a.* from (
    select d.*,(select s.USERNAME from v$session s where s.sid = d.sid) username,(select s.SERIAL# from v$session s where s.sid = d.sid) SERIAL# from dba_jobs_running d
    ) a

    This is what I use anyways
  • 6. Re: How to drop a job?
    32404 Explorer
    Currently Being Moderated
    Hi

    Try to use undocumented package dbms_ijob (proc: remove)

    Paul
  • 7. Re: How to drop a job?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Paul G. Matuszyk wrote:

    Try to use undocumented package dbms_ijob (proc: remove)
    And what does this make the code using an undocumented call - more robust?

    And what does it make a schema that have access to undocumented system packages - more secure?
  • 8. Re: How to drop a job?
    32404 Explorer
    Currently Being Moderated
    I am answering questions and not asking another one - if you want to ask if it is more secure start a new thread. Original question was how to remove it - i gave the answer.
    Paul

    Edited by: Paul G. Matuszyk on Nov 2, 2011 3:45 PM
  • 9. Re: How to drop a job?
    Max Seleznev Explorer
    Currently Being Moderated
    You already received plenty of good advise. One thing I haven't seen is stopping the job from further executions with
    SQL> exec dbms_job.broken(<job_id>, TRUE);
    SQL> commit;
    You can check the status of a job in dba_jobs.broken column (Y/N). If the job is currently running you can follow above advise and kill its process then remove the job.

    There is nothing wrong with using DBMS_IJOB package as Paul suggested. It saves a lot of time if the job is in another schema. You can find enough information on this package on Metalink. It also could be useful in other situations like creating a job with predefined JOB_ID. As far as I know it's used internally by import.
  • 10. Re: How to drop a job?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Paul G. Matuszyk wrote:
    I am answering questions and not asking another one - if you want to ask if it is more secure start a new thread. Original question was how to remove it - i gave the answer.
    And I questioned the validity of your answer as IMO it is a bad hack that should not be considered.
  • 11. Re: How to drop a job?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Max Seleznev wrote:

    There is nothing wrong with using DBMS_IJOB package as Paul suggested. It saves a lot of time if the job is in another schema.
    So opening a giant security hole, allowing a user or application schema to execute a job as sys, with the ability to do anything, is not wrong?

    I would not only call it wrong. I would call it foolish and just plain stupid. It is an offense that can get you fired when security is compromised.

    If you want to expose sys functions to user land then that needs to be implemented via trusted code wrappers that rigidly control access to, and usage of, that functionality - together will full auditing.
  • 12. Re: How to drop a job?
    Max Seleznev Explorer
    Currently Being Moderated
    That's a pretty strong wording.

    Nowhere in OP post or in the reply it says that any privileges have to be granted to anyone. There is a good chance OP already has DBA privileges or can connect as sys. Besides there're situations when one has no other choice than to use DBMS_IJOB package because of its unique functionality.
  • 13. Re: How to drop a job?
    834783 Newbie
    Currently Being Moderated
    Thanks, this worked...

    In SQLPLUS:

    SELECT JOB FROM DBA_JOBS WHERE JOB=61;

    EXEC SYS.dbms_ijob.remove(61);
    COMMIT;

    SELECT JOB FROM DBA_JOBS WHERE JOB=61;
  • 14. Re: How to drop a job?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Max Seleznev wrote:
    That's a pretty strong wording.
    Being fired after a disciplinary because you've opened a security hole that was exploited, is a lot worse... don't you think? ;-)
    Nowhere in OP post or in the reply it says that any privileges have to be granted to anyone. There is a good chance OP already has DBA privileges or can connect as sys.
    It is about security principles. Neither you or Paul mentioned the security issues that goes with using this system package. Someone (even the OP perhaps) is bound to interpret this as being a kewl package to use, better that just plain old DBMS_JOB, and then proceed to create and open barn doors in the security of that database. Not realising the implications of exposing system packages to user land applications and code.
    Besides there're situations when one has no other choice than to use DBMS_IJOB package because of its unique functionality.
    That is why one write wrappers - to provide this type of functionality, but in a secure and controlled fashion.
1 2 Previous Next

Legend

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