md_at_burning.de (Marc) wrote in message news:<c65d06e5.0308080218.3d38f6ba_at_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.
Received on Fri Aug 08 2003 - 11:07:25 CDT
