查找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()
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.
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属性
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.