DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','164.38.197.2:25');

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','himanshu.sharma@.com');



SET serveroutput ON
 declare
 v_att VARCHAR2(64);
 v_att2 varchar2(64);
 BEGIN
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
 dbms_output.put_line('server: ' || v_att);
 dbms_output.put_line('sender: ' || v_att2);
 END;
 /

 Connecting to the test user to create a sample job and created the test_mail job,
 that will start after 10 seconds from now.

 BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
 job_name => 'TEST_MAIL',
 job_type => 'PLSQL_BLOCK',
 job_action => 'begin
 for i in 1..200 loop
 for j in 1..200 loop
 null;
 end loop;
 end loop;
 end;
 ',
 start_date => systimestamp + interval '10' second ,
 repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
 enabled => True,
comments => '');
END;
 /


 Added the mail notification to the job, we have altered the value of the sender, so the default sender will not be used.
 Also we used job_all_events that will send mail notification for all events:

  BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
 job_name => 'TEST_MAIL',
 recipients => 'himanshu.sharma@.com',
 sender => 'do_not_reply@.com',
 subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
 body => '%event_type% occurred at %event_timestamp%. %error_message%',
 events => 'job_all_events');
 END;
 /



Set Tablespce

DBMS_SERVER_ALERT.SET_THRESHOLD(
   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_GT,
   warning_value           => '85',
   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_GT,
   critical_value          => '90',
   observation_period      => 1,
   consecutive_occurrences => 1,
   instance_name           => NULL,
   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
   object_name             => 'SYSAUX');
END;