Solved

Track scheduled jobs in database

Posted on 2011-09-16
12
335 Views
Last Modified: 2012-05-12

I wanted to track scheduled jobs run in my schema. Is there any view/table that I can use. If so, how do I do that. Please advise
0
Comment
Question by:d27m11y
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 250 total points
Comment Utility
How are those jobs set up?  Oracle10 and Oracle11 databases support two different database job schedulers.  The older one is DBMS_JOB and the those jobs are visible in the view: dba_jobs.  The newer (and more-complex) one is DBMS_SCHEDULER, and those are visible in the views:
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_PROGRAMS
DBA_SCHEDULER_SCHEDULES
etc.  (The are more views named: "DBA_SCHEDULER...").

You can use this query to find them:

select owner, view_name from all_views
where view_name like '%SCHED%' and owner = 'SYS'
order by 1, 2;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
And if you have just schema-level privileges and not DBA privileges, use the views named: "USER..." instead of "DBA...".  (Note: there is no "owner" column in the "USER..." views, since the owner is known then, it is the schema you logged in with.)
0
 

Author Comment

by:d27m11y
Comment Utility

We are using Oracle 11g and DBMS_SCHEDULER. Can I use dba_jobs?
0
 

Author Comment

by:d27m11y
Comment Utility
and yes, I do have DBA privileges. Can you please advise if there are any columns in the tables/views you have suggested which indicate, start time, end time, last run time and if the job was successful or not.

Thanks for your help!
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
as noted above - dba_jobs is for processes scheduled through dbms_jobs package only

dba_jobs view will not give you individual run times or status other than a failure count

the other views are for processes scheduled through dbms_scheduler only


the two packages perform related but incompatible functions.
dbms_scheduler is the oracle recommended method of scheduling for most operations

dba_jobs is still around for backward compatibility and for operations that you can't use dbms_scheduler (such as transaction depending scheduling )

0
 

Author Comment

by:d27m11y
Comment Utility

what should be the table/view and the columns that could be used to track the jobs scheduled thru dbms_scheduler, can you please advise
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Provided in the very first post:  http:#a36549574
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
look in the views markgeer pointed out in the first post.
0
 

Author Comment

by:d27m11y
Comment Utility

Can I say -

select owner, job_name, start_date, state,
 FAILURE_COUNT,NEXT_RUN_DATE
 from DBA_SCHEDULER_JOBS where owner ='xxx'
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
"Can I use dba_jobs [in Oracle11g]?"

Yes

Should you?  That is the issue.  It is there for backwards compatability for systems that have been developed for Oracle9 (or earlier) when DBMS_JOB was the only database scheduler, then upgraded to Oracle11.  It is a simpler scheduler to use though than the newer, more-complex DBMS_SCHEDULER.


0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
I'm going to guess you're looking for these two in particular


DBA_SCHEDULER_JOBS  - this will give detail about the last time each job was run

DBA_SCHEDULER_JOB_RUN_DETAILS -  this will give history of job executions
0
 

Author Closing Comment

by:d27m11y
Comment Utility
Could get my issue resolved
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now