We have two Oracle database servers. One for production with one instance on it, and another one with multiple instances for Dev, Test, etc. I have some jobs that run on production and email me when they fail. I had originally set the sender to be something like [email protected], so that I knew what environment the error was coming from.

Problem is when we copy production to one of the test or dev environments to get them up to date, then the job errors out for whatever reason, but it says it is coming from [email protected] which is incorrect.

I would like the notification email to pick up the servername and instance name that it is sent from, so if it comes from test it would be [email protected]. If that is not possible, then it would be fine to get this info in the subject line or body of the email.

I can get the server and instance name pretty easy with

SELECT  host_name, instance_name
FROM    v$instance;

,but I can't figure out how to get this to be used in the job email notifications.

I set these jobs up using the sql developer wizard, so if you can show me how to change it with that would be fine, or with pl/sql code, I am ok with either.

If none of this is possible, then is there I way I can make the jobs to not send out once they get to the non production environments at least because I really don't care if they fail there.

  • How are you sending the emails? Commented Oct 14, 2016 at 15:33
  • DBMS_SCHEDULER.add_job_email_notification Commented Oct 14, 2016 at 18:32

2 Answers 2


SYS_CONTEXT can be used to extract the host name of the database server and the instance name.

You simply call dbms_scheduler.add_job_email_notification in order to add the email notification like the following:

DBMS_SCHEDULER.add_job_email_notification (
  job_name => <JOBNAME>,
  recipients => <EMAIL>,
  subject => 
    ' - Scheduler Job Notification-%job_owner%.%job_name%-%event_type%', 
  events => 'job_failed');

The 12c documention for all of the SYS_CONTEXT parameters can be found here.

  • Awesome! Thanks, that did it. Was able to use that to generate the send from address I wanted. Worked like a charm! Commented Oct 14, 2016 at 18:32

when setting up job_notifications DO NOT set a value for the sender. instead set the scheduler attribute email_sender for your db.

the default email_sender will be the value provided for the email_sender as shown below. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','[email protected]');

  • This doesn't really answer the question of dynamically obtaining server and instance names at run time.
    – mustaccio
    Commented Aug 6, 2021 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.