Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Track scheduled jobs in database


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
d27m11y
Asked:
d27m11y
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
d27m11yAuthor Commented:

We are using Oracle 11g and DBMS_SCHEDULER. Can I use dba_jobs?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
d27m11yAuthor Commented:
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
 
sdstuberCommented:
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
 
d27m11yAuthor Commented:

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
 
slightwv (䄆 Netminder) Commented:
Provided in the very first post:  http:#a36549574
0
 
sdstuberCommented:
look in the views markgeer pointed out in the first post.
0
 
d27m11yAuthor Commented:

Can I say -

select owner, job_name, start_date, state,
 FAILURE_COUNT,NEXT_RUN_DATE
 from DBA_SCHEDULER_JOBS where owner ='xxx'
0
 
Mark GeerlingsDatabase AdministratorCommented:
"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
 
sdstuberCommented:
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
 
d27m11yAuthor Commented:
Could get my issue resolved
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now