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: md@burning.de (Marc)
Newsgroups: comp.databases.oracle.server
Subject: Terminating a Job
Date: 8 Aug 2003 03:18:35 -0700
Organization: http://groups.google.com/
Lines: 41
Message-ID: <c65d06e5.0308080218.3d38f6ba@posting.google.com>
NNTP-Posting-Host: 217.82.33.135
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060337916 24233 127.0.0.1 (8 Aug 2003 10:18:36 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 10:18:36 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240083

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