Re: HELP: How to stop a dbms job gracefully

Xuewei Zhou wrote:
>
> We have a problem with one of the jobs scheduled to run periodically
> using DBMS_JOB packages. It shows up in dba_jobs_running but it is not
> doing anything. We had to shutdown and restart the database to have
> this job working again. If you know how to stop a running job, please
> help. We are looking for a short term solution that can stop a hung job
> and run it again at the next interval without shuting down the database
> while we are working on the long term solution.
>
> Thanks.
>
> LuHuaron_at_usa.redcross.org

Every job establishes a session. Identify the session in the v$session and kill it. Maybe, the job is waiting for some locks to be released. Run server manager to identify these. As for locks taken by the job itself, these can be identified by querying the v$lock table for lock_mode = 'JQ'. ( digressed a bit ).

dbms_job.remove( job_no ) will probably not do the trick in killing the job. You can break it by dbms_job.broken( job_no, true ) and checking whats going wrong and reactivating it at a later stage.

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.