怎么样修改SCHEDULE的repeat_interval

查找job的内容：

1、查找PROGRAM_NAME 的内容
hbjzt@LMIS> select owner,type,text,name from dba_source where name = upper('p_del_aud' )  
  2  /

OWNER        TYPE                     TEXT                              NAME
--------------- ------------------------------------ ------------------------------------------------------------ -----------------------------------
SYS        PROCEDURE                 procedure p_del_aud is                      P_DEL_AUD
SYS        PROCEDURE                     begin                                P_DEL_AUD
SYS        PROCEDURE                         delete   sys.aud$  where ntimestamp# < to_timestamp(to_ P_DEL_AUD
                             char(sysdate- 30,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:m
                             i:ss') ;

SYS        PROCEDURE                         commit;                            P_DEL_AUD
SYS        PROCEDURE                     end p_del_aud ;                         P_DEL_AUD


PROGRAM_NAME 的type为PROCEDURE

2.查job 具体的定义
hbjzt@LMIS> select owner,job_name,job_type,PROGRAM_NAME,PROGRAM_OWNER,job_creator,client_id,job_type,schedule_name,start_date,end_date,repeat_interval from dba_scheduler_jobs where PROGRAM_NAME ='P_DEL_AUD'
  2  /
no rows selected

col  PROGRAM_NAME for a50
col JOB_NAME for a30
col PROGRAM_NAME for a50
col job_action for a50
select   OWNER,JOB_NAME,JOB_CREATOR,PROGRAM_OWNER,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE,PROGRAM_NAME ,job_action,REPEAT_INTERVAL from dba_scheduler_jobs  where upper(job_action)='P_DEL_AUD' ;
OWNER        JOB_NAME         JOB_CREATOR PROGRAM_OWN SCHEDULE_OWNER SCHEDULE_NAME   SCHEDULE_TYPE PROGRAM_NA JOB_ACTION REPEAT_INTERVAL
--------------- -------------------- ----------- ----------- -------------- --------------- ------------- ---------- ---------- ----------------------
SYS        J_DEL_AUD         SYS                            CALENDAR             p_del_aud    freq=daily;interval=30



3.查看历史记录
 select log_id,log_date ,owner,job_name,job_subname,status from   DBA_SCHEDULER_JOB_LOG  where  job_name = 'J_DEL_AUD'
     LOG_ID LOG_DATE        OWNER        JOB_NAME                       JOB_SUBNAME        STATUS
---------- -------------------- --------------- -------------------------------------------------- -------------------- ----------
   3678545 24-JUL-16 09.57.50.8 SYS        J_DEL_AUD                                SUCCEEDED
       17000 AM +08:00
       

4.清除job日志
exec DBMS_SCHEDULER.PURGE_LOG(JOB_NAME=>'MGMT_CONFIG_JOB')

 
5. 清除所以job的日志：
 exec DBMS_SCHEDULER.PURGE_LOG()

6.修改scheduler.repeat_interval属性

job_name = MY_JOB1
exec DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'MY_JOB1',attribute => 'repeat_interval',value => 'FREQ=HOURLY; INTERVAL=4');

sys@R2> exec DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'MY_JOB1',attribute => 'repeat_interval',value => 'FREQ=HOURLY; INTERVAL=4');
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'MY_JOB1',attribute => 'repeat_interval',value => 'FREQ=HOURLY; INTERVAL=4'); END;
*
ERROR at line 1:
ORA-27488: unable to set REPEAT_INTERVAL because SCHEDULE_NAME, EVENT_SPEC was/were already set
ORA-06512: at "SYS.DBMS_ISCHED", line 4426
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2861
ORA-06512: at line 1

col  PROGRAM_NAME for a50
col JOB_NAME for a30
col PROGRAM_NAME for a50
col job_action for a50
 select   OWNER,JOB_NAME,JOB_CREATOR,PROGRAM_OWNER,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE,PROGRAM_NAME ,job_action,REPEAT_INTERVAL from dba_scheduler_jobs  where upper(job_name)='&1' ;
Enter value for 1: MY_JOB1
old   1: select   OWNER,JOB_NAME,JOB_CREATOR,PROGRAM_OWNER,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE,PROGRAM_NAME ,job_action,REPEAT_INTERVAL from dba_scheduler_jobs    where upper(job_name)='&1'
new   1: select   OWNER,JOB_NAME,JOB_CREATOR,PROGRAM_OWNER,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE,PROGRAM_NAME ,job_action,REPEAT_INTERVAL from dba_scheduler_jobs    where upper(job_name)='MY_JOB1'

OWNER        JOB_NAME               JOB_CREATOR PROGRAM_OWN SCHEDULE_OWNER SCHEDULE_NAME   SCHEDULE_TYPE PROGRAM_NAME                       JOB_ACTION            REPEAT_INTERVAL
--------------- ------------------------------ ----------- ----------- -------------- --------------- ------------- -------------------------------------------------- -------------------------------------------------- ----------------------
SYS          MY_JOB1                SYS              SYS           SYS              DAY_WIN             WINDOW          MY_PRO1



a) disable it
exec dbms_scheduler.disable('MY_JOB1');

b) unschedule it (you are changing the schedule)
exec dbms_scheduler.set_attribute_null('MY_JOB1','DAY_WIN');
ORA-27469: DAY_WIN is not a valid job attribute
ORA-06512: at "SYS.DBMS_ISCHED", line 4370
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2905
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3028
ORA-06512: at line 1

repeat_interval

sys@R2> exec dbms_scheduler.SET_ATTRIBUTE_NULL('MY_JOB1','SCHEDULE_NAME');     /* SCHEDULE_NAME 字段名 */

PL/SQL procedure successfully completed.

c) custom schedule it
exec DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'MY_JOB1', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');

d) enable it
exec dbms_scheduler.enable( 'MY_JOB1' );











https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:652425700346984666
from ask tom :
How to change the auto stats collection job (GATHER_STATS_JOB) in 10GR2? It is created and scheduled by default to run at 10PM, we like to schedule it to run at different time, say 8PM on Friday and Saturday.

I tried this

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'GATHER_STATS_JOB',
attribute => 'repeat_interval',
value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');
END;
/

and get error:

ORA-27488: unable to set REPEAT_INTERVAL because SCHEDULE_NAME, EVENT_SPEC was/were already set
ORA-06512: at "SYS.DBMS_ISCHED", line 2834
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1847
ORA-06512: at line 2

What else I need to do to chenge it?

Thanks in advance.

and we said...
You want to

a) disable it
b) unschedule it (you are changing the schedule)
c) custom schedule it
d) enable it


sys%ORA10GR2> exec dbms_scheduler.disable( 'GATHER_STATS_JOB' );

PL/SQL procedure successfully completed.

sys%ORA10GR2> exec dbms_scheduler.set_attribute_null('gather_stats_job','schedule_name');

PL/SQL procedure successfully completed.

sys%ORA10GR2> exec DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'GATHER_STATS_JOB', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');

PL/SQL procedure successfully completed.

sys%ORA10GR2> exec dbms_scheduler.enable( 'GATHER_STATS_JOB' );

PL/SQL procedure successfully completed.