Terminating a Job

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 Received on Fri Aug 08 2003 - 05:18:35 CDT