Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sn-xit-03!sn-xit-06!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: Daniel Morgan <damorgan@exxesolutions.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Terminating a Job
Date: Fri, 08 Aug 2003 08:46:24 -0700
Organization: EXE
Message-ID: <3F33C5D0.78DDBE14@exxesolutions.com>
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
References: <c65d06e5.0308080218.3d38f6ba@posting.google.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yasure!unknown@pond178.drizzle.com
X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
X-Complaints-To: abuse@supernews.com
Lines: 54
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240109

Marc wrote:

> 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

Set up your UTL_SMTP procedure as an autonomous transaction with PRAGMA
AUTONOMOUS_TRANSACTION. That may keep it from hanging the job. I'd be
interested in knowing whether it works.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)