Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sjc70.webusenet.com!news.webusenet.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: rgaffuri@cox.net (Ryan Gaffuri)
Newsgroups: comp.databases.oracle.server
Subject: Re: Terminating a Job
Date: 8 Aug 2003 09:07:25 -0700
Organization: http://groups.google.com/
Lines: 46
Message-ID: <1efdad5b.0308080807.76813fce@posting.google.com>
References: <c65d06e5.0308080218.3d38f6ba@posting.google.com>
NNTP-Posting-Host: 198.80.171.28
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060358846 6569 127.0.0.1 (8 Aug 2003 16:07:26 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 16:07:26 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240115

md@burning.de (Marc) wrote in message news:<c65d06e5.0308080218.3d38f6ba@posting.google.com>...
> Hi,
> 
> we are using utl_smtp for sending emails directly from out database.
> for performance issues the send-procedure uses dbms_job and schedules
> one job per mail.
> 
> sometimes a kind of network-problem occures while a job is sending out
> a mail which implicies, the utl_smtp is hanging and does not give the
> control back to the main procedure. so the job never terminates by
> itself; it is still marked as "running".
> 
> The job remains in the queue, is visible in dba_jobs_running and got a
> lock in v$lock (type="JQ").
> 
> my problem:
> i would like to kill this hanging jobs without bouncing the
> database...
> 
> what i've tried:
> 
> 1. mark the job as broken (with dbms_job.broken)
> 2. disconnect the session (with alter system)
> 3. remove the job from the jobqueue (with dbms_job.remove)
> 
> but without a positive result: the session is marked as killed, but
> the job is still visible in dba_jobs_running and the lock is not
> released.
> 
> this leads to the situation, that with 32 (or 36?) jobs hanging, no
> more additional jobs will be executed, because all SNP-processes are
> busy.
> 
> 
> we are using oracle 8.1.7.4 on windows 2000, migration to 9i (where a
> timeout-parameter in utl_smtp exists) is currently not an option :(
> 
> any ideas?
> 
> Best Regards
> 
> Marc

i dont know if this is the same with jobs but i think it is. before
the session can be killed, it needs to rollback all transactions. if
you have alot, it could take a while.