Hi all!
There have been several posts lately regarding how to
kill or remove jobs from the job queue once they're
running, so here are the steps.
Background
- Removing a job from the job queue, removes it from
the queue, but does NOT abort the job if it is
currently running (i.e. in DBA_JOBS_RUNNING).
- If the job# in dba_jobs_running doesn't exist in
dba_jobs, then the job has already been removed from
the job queue.
- You can only manage your own jobs, even if you're a
DBA user. Someone else mentioned using BECOME USER to
manage another user's jobs; sounds like a great idea,
but I haven't had a chance to try that to see if it
works.
- Jobs in the job queue are not run by the SNP
processes when the db is started up in restricted mode
or when JOB_QUEUE_PROCESSES=0, but may be run
manually.
To kill a currently running job
- First you must break or remove the job in the job
queue, otherwise the job will just start right back up
again the next time the SNP processes check for jobs
to run (based on job_queue_interval).
As the job owner:
execute dbms_job.broken(<job#>,true);
or
execute dbms_job.remove(<job#>);
- Kill the SNP process at the OS level. You can
identify the process to kill using the SID from
DBA_JOBS_RUNNING:
select s.sid, p.spid from v$session s, v$process p
where s.paddr=p.addr and s.sid=<sid>;
p.spid is the OS process ID
- As with any session that is killed, it will need
to be rolled back and cleaned up.
To restart a broken job
A job will be marked as unbroken the next time it is
successfully run or when it is marked as unbroken.
As the job owner:
execute dbms_job.run(<job#>);
or
execute dbms_job.broken(<job#>,false);
Also check out Note 61730.1 "Using the DBMS_JOB
Package"
HTH,
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sat Jun 02 2001 - 10:40:58 CDT
