Delete Oracle email notification

Google?

Try the drop_job procedure:

http://www.orafaq.com/wiki/DBMS_SCHEDULER

Look for name of the created job in

SELECT * FROM user_scheduler_jobs

then delete it via

BEGIN
  EXEC DBMS_SCHEDULER.DROP_JOB('job_name');
END;

or maybe just disable it:

BEGIN
  EXEC DBMS_SCHEDULER.DISABLE('job_name');
END;

Pretty sure I already posted drop_job but thanks for the repost.

Thank you for your help.
I made a mistake: I gave same name to the scheduled job (The job which runs a stored procedure) and the email notification job.
SELECT * FROM user_scheduler_jobs
Above retrieves the job name but
BEGIN
  EXEC DBMS_SCHEDULER.DROP_JOB('job_name');
END;

The above deletes the actual job but not the email notification job.

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/scheduse008.htm#CIAJJAJE

BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'job_name',
END;

Oops just realized a typo in my previous post.  Should have been a closing parenthesis, not a comma, after 'job_name' :

BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'job_name')
END;

Argh, now I missed the ending ";"

BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'job_name');
END;

Thanks a lot.

I am testing this.
I get no way to find the existing email notification jobs. The following query executes (no matter the job_name value is correct or incorrect).
Waiting for next email notification. If it does not come it should have worked...

BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'MY_JOB');
END;

You should be able to look up existing notifications via:

select * from user_scheduler_notifications;

Thanks a lot.
select * from user_scheduler_notifications;
The above query returns nothing. Strange that I am getting email notification from this Oracle schema every hour...

hmmm maybe another user owns the notification job.  As a user with system privileges you can do:

select * from all_scheduler_notifications;

- i usually use this SQL statement that i found from an article in Toolbox to view list of scheduled job by querying to dba_jobs view :

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, 
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, 
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

Select allOpen in new window

Many thanks.
I do not see jobs with the query: select * from all_scheduler_notifications;

I do not have DBA privilege. I am asking the DBA to use the above query to look into any broken job.
Thanks.

tooki,
- you can ask your DBA to run my ID: 35372327 query on dba_jobs view to list all job that have been scheduled. i believe you will find the job listed there.
- then in my next posting is the instruction on how to disable the job (not dropping it) so that it won't run again.

Thank you.
I asked the DBA the same. But they are slow.
I will get back to you. Many thanks.

Thank you.