From: Sridhar Subramaniam Subject: Re: HELP: How to stop a dbms job gracefully Date: 1996/11/11 Message-ID: <32871C34.2BCB@ozemail.com.au>#1/1 references: <55vt89$rep@cm1.umd.edu> to: Xuewei Zhou content-type: text/plain; charset=us-ascii organization: OzEmail Ltd - Australia mime-version: 1.0 newsgroups: comp.databases.oracle,comp.databases.oracle.server x-mailer: Mozilla 3.0 (WinNT; I) Xuewei Zhou wrote: > > We have a problem with one of the jobs scheduled to run periodically > using DBMS_JOB packages. It shows up in dba_jobs_running but it is not > doing anything. We had to shutdown and restart the database to have > this job working again. If you know how to stop a running job, please > help. We are looking for a short term solution that can stop a hung job > and run it again at the next interval without shuting down the database > while we are working on the long term solution. > > Thanks. > > LuHuaron@usa.redcross.org Every job establishes a session. Identify the session in the v$session and kill it. Maybe, the job is waiting for some locks to be released. Run server manager to identify these. As for locks taken by the job itself, these can be identified by querying the v$lock table for lock_mode = 'JQ'. ( digressed a bit ). dbms_job.remove( job_no ) will probably not do the trick in killing the job. You can break it by dbms_job.broken( job_no, true ) and checking whats going wrong and reactivating it at a later stage. -- Cheers Sridhar Subramaniam Avion Consulting Services Sydney - Australia Email : avion@ozemail.com.au Disclaimer : All opinions are truly and just mine.