We help IT Professionals succeed at work.

Auto gather stats 11g

vkchaitu82
vkchaitu82 used Ask the Experts™
on
Hi experts,
We are using oracle 11g. We have automatic gather statistics scheduled at 11.00 PM. My requirement is to schedule a oracle job daily at 11.00PM and check data dictionary views at a interval of 5mins until i find if the job is completed.If i find the job is completed shoot an email to xyz@gmail.com

I dont need the script for scheduling oracle job and shooting the email.I need core piece script to find if the job got completed etc.
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
Rather than checking every 5 minutes, best option is to add the code into the job itself to send email out via the PL/SQL SMTP API.

Is that an option?
Top Expert 2009
Commented:
For an external monitor, I would try querying DBA_SCHEDULER_JOBS for your specific job, and a state = 'COMPLETED'

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2049.htm#i1587306
Most Valuable Expert 2011
Top Expert 2012
Commented:

look in dba_objecs for the object_id of your job

select object_id from dba_objects where obect_type = 'JOB' and object_name = 'YOUR_JOB'

select * from V$SCHEDULER_RUNNING_JOBS  where job_id = <<< result from previous query>>>

any job that is in the running jobs view is, as the name implies, still running

Top Expert 2009

Commented:
Oh, yes forgot about that view, haven't been working with jobs lately.

One more thing, you'll need your script to detect the state change, when the job appears (changes to RUNNING), the script then needs to poll until it sees COMPLETED.

Same if using the view V$SCHEDULER_RUNNING_JOBS, when the job apears, your script needs to go into a waiting state, then send the email when it goes away.

So keep a toggle variable so you don't spam.

Author

Commented:
Any options of using DBA_AUTOTASK_JOB_HISTORY?? Please suggest

Author

Commented:
I think in 11g the below tables should give the required info.. any ideas??

DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_OPERATION
DBA_AUTOTASK_SCHEDULE
DBA_AUTOTASK_TASK
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY

Top Expert 2009
Commented:
>>I think in 11g the below tables should give the required info.. any ideas??

Have you read our recommendations? We've already pointed you to the tables that allow live tracking of jobs.