Re: Terminating a Job

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0308080807.76813fce_at_posting.google.com>...
> 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.

thanks for your hint, but i don't think, that this is the reason... some jobs are hanging around with state "killed" since six days ... and within the procedure, just two updates (on single lines) are made.

regards

marc Received on Sat Aug 09 2003 - 05:27:56 CDT