Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1150
  • Last Modified:

Trying to kill a dbms_job and prevent it from running again

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

  • 2
1 Solution
/* 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


---------End code------------------------------------
KathysFriendDBAAuthor Commented:
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.
Possiby there is a missing COMMIT statement (in my example too! :)))
Mark GeerlingsDatabase AdministratorCommented:
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.

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now