Trying to kill a dbms_job and prevent it from running again

Posted on 2009-02-13
Last Modified: 2013-12-18
I located the job, sid, serial#, and spid of the job that was running.
I logged on as the user who created the job and successfully ran EXEC DBMS_JOB.BROKEN(10483,TRUE);
I successfully rant ALTER SYSTEM KILL SESSION '156,6680';
On the server I ran kill '9 656940 and confirmed the process was gone.
But it immediately spawned a new job/process
When I log on as the user who created the job and query user_jobs
I see that job 10483 just started even though it has a next-date of 4000-01-01 and broken = Y
As DBA, when I query dba_jobs the job 10483 has a next_date of 2009-02-13 and broken = N

Question by:KathysFriend
    LVL 47

    Expert Comment

    /* use this to see which jobs are in
    ** the queue

    select job,substr(what,1,200) from dba_jobs;

    -----------End code----------------------------------

    ** use this to delete a job from the queue
    ** The number should be the same as the number
    ** returned from dbms_job.submit

       DBMS_JOB.REMOVE (1);

    ---------End code------------------------------------

    Author Comment

    I logged on as the user and successfully removed the job and then I killed the session.
    Then it showed nothing under user jobs and there is nothing running for that user.
    It still showed up under dba_jobs - I'm not sure why.
    Then low and behold - the job reappears under user_jobs and is now running again.
    LVL 47

    Accepted Solution

    Possiby there is a missing COMMIT statement (in my example too! :)))
    LVL 34

    Expert Comment

    Is it running now with the same job number or a new job number?  If it has a new job number now, then something in the application creates it automatically if it isn't there.

    What does the job do?  Does it run a named, stored procedure, or an anonymous PL\SQL block?  If it runs a named, stored procedure you could consider dropping that.  Of course, that could break other things in the application.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now