who to find all jobs running in Oralce9i

[lhugh]

Is there a view to find out what jobs are scheduled in an Oracle database?

And how do I turn off a job(s)? I think there is a DBMS package but I forgot which one it is

Thanks

 

[SantaMufasa]

L,

The data dictionary views you at which you can look are:
USER_JOBS
ALL_JOBS
DBA_JOBS

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:10 (28Nov04) UTC (aka "GMT" and "Zulu"),
@ 13:10 (28Nov04) Mountain Time

 

[SantaMufasa]

L,

I realised that you may need a script or two to make the best use of the *_JOBS views. (These are compliments of James Koopmann.) Here is one that shows you scheduled jobs:

set linesize 250
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.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 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) j;

This script shows jobs that are actually running:

set linesize 250
col sid            for 9999     head 'Session|ID'
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,
       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;

To get rid of a job from *_JOBS (provided that the job is not currently running), you can:

execute DBMS_JOB.REMOVE (<job_number>)

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:27 (28Nov04) UTC (aka "GMT" and "Zulu"),
@ 13:27 (28Nov04) Mountain Time

 

[lhugh]

thanks Dave