Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Track scheduled jobs in database

Posted on 2011-09-16
12
Medium Priority
?
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 74

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 77

Expert Comment

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

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 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

719 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