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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
slightwv (䄆 Netminder) Commented:
Pretty sure I already posted drop_job but thanks for the repost.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.