Most of the time you want to configure a job to send e-mail notifications about its state. The different states that a job can raise an event and thus send an email are :
job_broken
job_chain_stalled
job_completed
job_disabled
job_failed
job_over_max_dur
job_run_completed
job_sch_lim_reached
job_started
job_stopped
job_succeeded
job_all_events --> all the above.
begin
dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server'); --> Using SMTP default port (25)
-- dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server:777'); --> Using SMTP 777 port
dbms_scheduler.set_scheduler_attribute('email_sender','myemail@myserver.com');
commit;
end;
/
begin
dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB',
recipients => 'myrecipient1@myemail.com,myrecipient2@myemail.com',
subject => 'Scheduler Job Notification : %job_owner%.%job_name%-%event_type%'
, body => '%event_type% occurred at %event_timestamp%. %error_message%',
events => 'job_all_events' );
commit;
end;
/
The previous command will send an email for all job events to the recipients specified using the default sender. use the following to specify sender and specific job states:
begin
dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB', recipients
=> 'myrecipient1@myemail.com, myrecipient1@myemail.com', sender =>
'db_job_notification@myemail.com', subject =>
'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%', body =>
'%event_type% occurred at %event_timestamp%. %error_message%', events =>
'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
commit;
end;
/
select job_name,
recipient,
event
from user_scheduler_notifications;
http://agstamy.blogspot.kr/2013/02/configure-db-scheduler-job-to-send-e.html
job_broken
job_chain_stalled
job_completed
job_disabled
job_failed
job_over_max_dur
job_run_completed
job_sch_lim_reached
job_started
job_stopped
job_succeeded
job_all_events --> all the above.
First of all we have to provide the DB Scheduler with the SMTP mail server to use and a default email sender to be used:
begin
dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server'); --> Using SMTP default port (25)
-- dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server:777'); --> Using SMTP 777 port
dbms_scheduler.set_scheduler_attribute('email_sender','myemail@myserver.com');
commit;
end;
/
This must be configured once.
Then assuming that a job named MYJOB has been created run this as the job owner.
begin
dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB',
recipients => 'myrecipient1@myemail.com,myrecipient2@myemail.com',
subject => 'Scheduler Job Notification : %job_owner%.%job_name%-%event_type%'
, body => '%event_type% occurred at %event_timestamp%. %error_message%',
events => 'job_all_events' );
commit;
end;
/
begin
dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB', recipients
=> 'myrecipient1@myemail.com, myrecipient1@myemail.com', sender =>
'db_job_notification@myemail.com', subject =>
'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%', body =>
'%event_type% occurred at %event_timestamp%. %error_message%', events =>
'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
commit;
end;
/
By using the following query you have information for which jobs email notifications have been setup.
select job_name,
recipient,
event
from user_scheduler_notifications;
REFERENCES
Oracle® Database Administrator's Guide 11g Release 2 (11.2)http://agstamy.blogspot.kr/2013/02/configure-db-scheduler-job-to-send-e.html