toooki
asked on
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:
https://www.experts-exchange.com/questions/26831451/Oralce-11g-Scheduled-Job-Email-notification.html
using:
DBMS_SCHEDULER.ADD_JOB_EMA IL_NOTIFIC ATION
But cannot locate example how to delete the same after creating?
I created on as in:
https://www.experts-exchange.com/questions/26831451/Oralce-11g-Scheduled-Job-Email-notification.html
using:
DBMS_SCHEDULER.ADD_JOB_EMA
But cannot locate example how to delete the same after creating?
Look for name of the created job in
SELECT * FROM user_scheduler_jobs
then delete it via
BEGIN
EXEC DBMS_SCHEDULER.DROP_JOB('j ob_name');
END;
or maybe just disable it:
BEGIN
EXEC DBMS_SCHEDULER.DISABLE('jo b_name');
END;
SELECT * FROM user_scheduler_jobs
then delete it via
BEGIN
EXEC DBMS_SCHEDULER.DROP_JOB('j
END;
or maybe just disable it:
BEGIN
EXEC DBMS_SCHEDULER.DISABLE('jo
END;
Pretty sure I already posted drop_job but thanks for the repost.
ASKER
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('j ob_name');
END;
The above deletes the actual job but not the email notification job.
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('j
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_NOTI FICATION (
job_name => 'job_name',
END;
BEGIN
DBMS_SCHEDULER.REMOVE_JOB_
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_NOTI FICATION (
job_name => 'job_name')
END;
BEGIN
DBMS_SCHEDULER.REMOVE_JOB_
job_name => 'job_name')
END;
Argh, now I missed the ending ";"
BEGIN
DBMS_SCHEDULER.REMOVE_JOB_ EMAIL_NOTI FICATION (
job_name => 'job_name');
END;
BEGIN
DBMS_SCHEDULER.REMOVE_JOB_
job_name => 'job_name');
END;
ASKER
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_NOTI FICATION (
job_name => 'MY_JOB');
END;
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_
job_name => 'MY_JOB');
END;
You should be able to look up existing notifications via:
select * from user_scheduler_notificatio ns;
select * from user_scheduler_notificatio
ASKER
Thanks a lot.
select * from user_scheduler_notificatio ns;
The above query returns nothing. Strange that I am getting email notification from this Oracle schema every hour...
select * from user_scheduler_notificatio
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_notification s;
select * from all_scheduler_notification
- 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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks.
I do not see jobs with the query: select * from all_scheduler_notification s;
I do not have DBA privilege. I am asking the DBA to use the above query to look into any broken job.
Thanks.
I do not see jobs with the query: select * from all_scheduler_notification
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.
- 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.
ASKER
Thank you.
I asked the DBA the same. But they are slow.
I will get back to you. Many thanks.
I asked the DBA the same. But they are slow.
I will get back to you. Many thanks.
ASKER
Thank you.
Try the drop_job procedure:
http://www.orafaq.com/wiki/DBMS_SCHEDULER