Link to home
Start Free TrialLog in
Avatar of sigma19
sigma19Flag for United States of America

asked on

Auto gather stats 11g

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.
 
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

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?
ASKER CERTIFIED SOLUTION
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of sigma19

ASKER

Any options of using DBA_AUTOTASK_JOB_HISTORY?? Please suggest
Avatar of sigma19

ASKER

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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial