Clearing Oracle job queue Options

[Optimus]

Hi all,

I am using Oracle 9.2 version and I had to run a stored procedure asynchronously. I was researching a little bit on this and found that Oracle jobs was a way to do it. Is this the only way or is there a way to run a stored procedure asynchronously?

Also, I had a question in using DBMS_JOBS package. Currently, if I decide to use jobs, I need to log new jobs every 6 minutes and the original transaction which logged the jobs has to continue without waiting for the jobs to finish. The problem with this is that the job queue can become very long after sometime. Is there a way to automatically remove the jobs from the job queue once it is finished?

Any pointers to this would be greatly helpful.

Thanks.

[burleson]

Hi Opt,

>> Is this the only way or is there a way to run a stored procedure asynchronously?

No, you can schedule a stored procedure to execute anytime in the future, even with sophisticated time rules:

http://www.dba-oracle.com/tips_oracle_dbms_job.htm

See Dr. Hall book "Oracle Job Scheduling" for complete details:

http://www.rampant-books.com/book_2005_1_scheduling.htm

****************************************************************************
>> Is there a way to automatically remove the jobs from the job queue once it is finished?

First, dbms_job is superceded by dbms_scheduler, with lots more features. . . .

http://www.oracle-training.cc/10g_457.htm

After they are done, sure, just nuke them with DBMS_JOB.REMOVE(JOB => nn)

For running jobs, you could write a shell script to:

1 - Cauture the SID and PID for all running jobs

2 - Break all jobs (EXEC DBMS_JOB.BROKEN(job#,TRUE)

3 - For each (SID, PID), issue an ALTER SYTEM KILL SESSION command (ALTER SYSTEM KILL SESSION 'sid,serial#';)

QUEUED JOBS
*************

For queued jobs, I would look at the advanced queueing, since that governs the queues, the dbms_aqadm and dbms_aq packages, plus Dr. Halls dba_jobs scripts

http://www.dba-oracle.com/t_dbms_aq.htm


0 - Stop all job execution: (ALTER SYSTEM SET job_queue_processes = 0;)

1 - Find all queued jobs (using dba_jobs)

2 - Break the jobs (using dbms_jobs)

3 - Remove the jobs (using dbms_jobs: DBMS_JOB.REMOVE(JOB => nn)

***************************

Find sesions running a job:

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;