20100630 scheduler
Oracle数据库10g schedule job的常用操作:
-- job 权限
grant create job to somebody;-- job 创建
begindbms_scheduler.create_job (
job_name => 'AGENT_LIQUIDATION_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'AGENT_LIQUIDATION.LIQUIDATION', --存储过程名
start_date => sysdate,
repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0', -- 按月,间隔为1个(月),每月1号,凌晨1点
comments => '执行代理商清分程序'
);
end;
/
参考:http://space.itpub.net/7607759/viewspace-612589
--job 修改 例子
beginsys.dbms_scheduler.disable(name => 'CMP_RAS.CMP_RAS_MOVE_INFO2TRAIN_PROC');
sys.dbms_scheduler.set_attribute(name => 'CMP_RAS.CMP_RAS_MOVE_INFO2TRAIN_PROC', attribute => 'start_date', value => to_date('22-11-2010 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
sys.dbms_scheduler.set_attribute(name => 'CMP_RAS.CMP_RAS_MOVE_INFO2TRAIN_PROC', attribute => 'comments', value => '每日制作密钥从运行导到培训');
sys.dbms_scheduler.enable(name => 'CMP_RAS.CMP_RAS_MOVE_INFO2TRAIN_PROC');
end;
/
-- job 执行时间测试
DECLAREstart_date date;
return_date_after date;
next_run_date date;
BEGIN
start_date := sysdate;--to_timestamp_tz('10-OCT-2004 10:00:00','DD-MM-YYYY HH24:MI:SS');
return_date_after := start_date;
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date,'yyyy-mm-dd HH24:MI:SS'));
return_date_after := next_run_date;
END LOOP;
END;
/
-- job 查询
select owner, job_name, state from dba_scheduler_jobs;select job_name, state from user_scheduler_jobs;
-- job 启用
begindbms_scheduler.enable('BACKUP_JOB');
end;
/
-- job 运行
begindbms_scheduler.run_job('COLA_JOB',TRUE); -- true代表同步执行
end;
/
-- job 停止(不太好用)
begindbms_scheduler.stop_job(job_name => 'COLA_JOB',force => TRUE);
end;
/
-- job 删除(对停job来说好用)
begindbms_scheduler.drop_job(job_name => 'COLA_JOB',force => TRUE);
end;
/
更详细参考 http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sched.htm#ARPLS138
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-705180/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-705180/