Avatar of vkchaitu82
vkchaitu82
 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.
 
Oracle Database

Avatar of undefined
Last Comment
mrjoltcola

8/22/2022 - Mon
mrjoltcola

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
mrjoltcola

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mrjoltcola

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

ASKER
Any options of using DBA_AUTOTASK_JOB_HISTORY?? Please suggest
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
vkchaitu82

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.