Home

Introduction

Oracle Scheduler allows database administrators to schedule and manage jobs efficiently. Email notifications can be configured to receive updates on job statuses, such as when jobs start, succeed, fail, or encounter issues. This feature ensures timely alerts and improves monitoring of automated processes.

Step 1: Configure Email Server and Default Sender

Begin by connecting to SQL*Plus as a privileged user and setting the email server and default sender.

Set the Email Server

SQL> EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25');

• Host: The SMTP server’s hostname or IP address.
• Port: The TCP port the SMTP server listens on (default is 25).

If this attribute is not set or configured incorrectly, the Scheduler cannot send email notifications.

Set the Default Email Sender

SQL> EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','DBMS_NOTIFICATION@organization.net');

Verify Email Server and Sender

DECLARE
  v_server VARCHAR2(64);
  v_sender VARCHAR2(64);
BEGIN
  DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_server);
  DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_sender);
  DBMS_OUTPUT.PUT_LINE('Server: ' || v_server);
  DBMS_OUTPUT.PUT_LINE('Sender: ' || v_sender);
END;
/

Step 2: Create a Sample Job

Connect to the test user and create a job. This example creates a job called TEST_MAIL, which performs a looping operation starting 10 seconds from now.

SQL> 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;
/

Step 3: Add Email Notifications

Configure email notifications for the job to receive updates on all job-related events.

SQL> BEGIN
  DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    job_name => 'TEST_MAIL',
    recipients => 'arvind@organization.net',
    sender => 'DBMS_NOTIFICATION@organization.net',
    subject => 'Scheduler Job Notification - %job_owner%.%job_name% - %event_type%',
    body => '%event_type% occurred at %event_timestamp%. %error_message%',
    events => 'job_all_events');
END;
/

Step 4: Verify Notifications

Use the USER_SCHEDULER_NOTIFICATIONS view to confirm the notification setup:

SQL> SELECT EVENT FROM USER_SCHEDULER_NOTIFICATIONS WHERE JOB_NAME='TEST_MAIL';

Output:

EVENT
-------------------
JOB_STARTED
JOB_SUCCEEDED
JOB_FAILED
JOB_BROKEN
JOB_COMPLETED
JOB_STOPPED
JOB_SCH_LIM_REACHED
JOB_DISABLED
JOB_CHAIN_STALLED
JOB_OVER_MAX_DUR

Step 5: Clean Up

After testing, you can remove the email notifications or drop the job as needed.

Remove Email Notifications

SQL> EXEC DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION('TEST_MAIL');

Drop the Job

SQL> BEGIN
  DBMS_SCHEDULER.DROP_JOB(
    job_name => 'TEST_MAIL',
    defer => FALSE,
    force => FALSE);
END;
/

Conclusion

Configuring email notifications for Oracle Scheduler jobs is an essential feature for proactive database monitoring. By setting up an SMTP server, defining default senders, and configuring job-specific notifications, database administrators can ensure timely updates on job statuses and quickly address issues. Follow the steps outlined above to set up and manage email notifications effectively.