Solved

Track scheduled jobs in database

Posted on 2011-09-16
12
347 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 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

630 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