0

I have the request to setup an eMail notification if the FRA usage of the database (Oracle 19c Standard Edition) exceeds a certain limit.

My idea is the following:

  1. Create a stored procedure that retrieves the current FRA utilization and if it is above a threshold, raise an exception
  2. Create an DBMS_SCHEDULER Oracle Job that calls that procedure every day
  3. Create DBMS_SCHEDULER.add_job_email_notification that fires an eMail if the job fails.

Unfortunately this doesn't seem to work because raising an exception in the stored procedure in (1) does not cause the job to fail.

Is there maybe an easier way to solve that or another way to trigger eMail notification for my purpose?

Thanks!

bluefox
  • 175
  • 3
  • 16

1 Answers1

0

You can try scheduling cron jobs using shell script if the database is running in the Linux environment or scheduler if the database is running in Windows environment.

MrGrEEN
  • 51
  • 3
  • It would be better to give more details about your answer. – Masoud Rahimi Oct 07 '21 at 09:03
  • The customer prefers to solve it purely inside Oracle database (if somehow possible) instead of creating external scripts. – bluefox Oct 07 '21 at 10:49
  • Step-1: Check SMTP parameter value is set in the Oracle Database refer: https://oracle-base.com/articles/10g/utl_mail-send-email-from-the-oracle-database Step-2: You can schedule the job using the DBMS_SCHEDULER with the email notification using below link https://oracle-base.com/articles/11g/scheduler-enhancements-11gr2 – MrGrEEN Oct 11 '21 at 04:20