Re: Terminating a Job

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_at_x.washington.edu
(replace 'x' with a 'u' to reply)