怎么样修改SCHEDULE的repeat

admin管理员组

文章数量:1794759

怎么样修改SCHEDULE的repeat

查找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' ); asktom.oracle/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.

本文标签： schedulerepeat