Delete Oracle email notification

Could anyone please tell me how to drop / delete an existing email notification job in Oracle 11gR2?

I created on as in:
http://www.experts-exchange.com/Database/Oracle/Q_26831451.html

using:
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

But cannot locate example how to delete the same after creating?
toookiAsked:
Who is Participating?
 
OP_ZaharinCommented:
- apart from delete/drop a job, you can also opt to disable a job. this command mark the job so that it won't start again.:
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE)

- next is to kill the session if its still running:
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

- the following SQL will give you information such as job, sid, serial#, and spid for you to bring down a job:
select j.sid,
s.spid,
s.serial#,
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 djr.SID, 
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, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Google?

Try the drop_job procedure:

http://www.orafaq.com/wiki/DBMS_SCHEDULER
0
 
johanntagleCommented:
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;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
slightwv (䄆 Netminder) Commented:
Pretty sure I already posted drop_job but thanks for the repost.
0
 
toookiAuthor Commented:
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.
0
 
johanntagleCommented:
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;

0
 
johanntagleCommented:
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;
0
 
johanntagleCommented:
Argh, now I missed the ending ";"

BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'job_name');
END;
0
 
toookiAuthor Commented:
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;
0
 
johanntagleCommented:
You should be able to look up existing notifications via:

select * from user_scheduler_notifications;
0
 
toookiAuthor Commented:
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...
0
 
johanntagleCommented:
hmmm maybe another user owns the notification job.  As a user with system privileges you can do:

select * from all_scheduler_notifications;
0
 
OP_ZaharinCommented:
- 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;

Open in new window


0
 
toookiAuthor Commented:
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.
0
 
OP_ZaharinCommented:
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.
0
 
toookiAuthor Commented:
Thank you.
I asked the DBA the same. But they are slow.
I will get back to you. Many thanks.
0
 
toookiAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.