to list administrator

From: Arslan Bahar <abahar_at_ford.com.tr>
Date: Sat, 02 Jun 2001 10:13:02 -0700
Message-ID: <F001.0031942B.20010602101521@fatcity.com>

i have been receiving each mail two times

-----Original Message-----
Sent: Saturday, June 02, 2001 7:30 PM
To: Multiple recipients of list ORACLE-L

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,

Anita

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Arslan Bahar
INET: abahar_at_ford.com.tr

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------