Solved

Track scheduled jobs in database

Posted on 2011-09-16
12
339 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 35

Accepted Solution

by:
Mark Geerlings earned 250 total points
ID: 36549574
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 35

Expert Comment

by:Mark Geerlings
ID: 36549600
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
ID: 36549645

We are using Oracle 11g and DBMS_SCHEDULER. Can I use dba_jobs?
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.

 

Author Comment

by:d27m11y
ID: 36549663
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
ID: 36549749
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
ID: 36549772

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
 
LVL 76

Expert Comment

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

Expert Comment

by:sdstuber
ID: 36549804
look in the views markgeer pointed out in the first post.
0
 

Author Comment

by:d27m11y
ID: 36549809

Can I say -

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

Expert Comment

by:Mark Geerlings
ID: 36549826
"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
ID: 36549828
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
ID: 36567627
Could get my issue resolved
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

785 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