Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Disable Oracle JOB

Posted on 2013-05-22
4
Medium Priority
?
797 Views
Last Modified: 2013-05-26
Hi,
I need disable the Oracle statistic SYS.GATHER_STATS_JOB for only these days: 27 - 28 and 29 MAY 2013

If I run:
EXECUTE DBMS_SCHEDULER.DISABLE (name => 'SYS.GATHER_STATS_JOB');

Open in new window

I disable the JOB but I'd like disable this job only for 27 - 28 and 29 MAY 2013

can I disable the job only for a few days?

if so, can you tell me what step I should do?

Thanks in advance!
0
Comment
Question by:ralph_rea
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 2000 total points
ID: 39187013
one approach is to schedule this
EXECUTE DBMS_SCHEDULER.DISABLE (name => 'SYS.GATHER_STATS_JOB');
for  27 MAY 2013 00:00:00 and the opposite
EXECUTE DBMS_SCHEDULER.ENABLE (name => 'SYS.GATHER_STATS_JOB');
for 30 MAY 2013 00:00:00.

Both jobs will be executed once.

HTH

Ivo Stoykov
0
 

Author Comment

by:ralph_rea
ID: 39187015
I Know that I can create a new named schedule:


dbms_scheduler.create_schedule( schedule_name => 'may_exclude',
repeat_interval => 'freq=monthly;bymonth=5;bymonthday=27,28,29;byhour=0;byminute=0;bysecond=0',
comments => 'named schedule to exclude 27,28,29 may');

Open in new window

but I don't know How can I associate this named schedule "may_exclude" to my job SYS.GATHER_STATS_JOB with EXCLUDE clause

Have you any idea?
0
 

Author Comment

by:ralph_rea
ID: 39187491
SELECT a.job_name, a.enabled, c.window_name, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
And a.schedule_name=b.window_group_name
And b.window_name=c.window_name;

JOB_NAME                       ENABL WINDOW_NAME                    REPEAT_INTERVAL
------------------------------ ----- ------------------------------ --------------------------------------------------
GATHER_STATS_JOB               TRUE  WEEKEND_WINDOW                 freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=
                                                                    0

GATHER_STATS_JOB               TRUE  WEEKNIGHT_WINDOW               freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=14
                                                                    ; byminute=0; bysecond=0

Open in new window

0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 39189972
depends how you;ve created the job, but you could state a schedule name
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_new_job3', 
   program_name        =>  'my_saved_program1', 
   schedule_name       =>  'my_saved_schedule1'); -- <-- 
END;
/

Open in new window

for more look here

HTH

Ivo Stoykov
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
What we learned in Webroot's webinar on multi-vector protection.
Via a live example, show how to take different types of Oracle backups using RMAN.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question