Oracle数据库10g schedule job的常用操作:
-- job 权限
grant create job to somebody;
-- job 创建
begin
dbms_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;
/
-- job 执行时间测试
DECLARE
start_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 启用
begin
dbms_scheduler.enable(''BACKUP_JOB'');
end;
/
-- job 运行
begin
dbms_scheduler.run_job(''COLA_JOB'',TRUE); -- true代表同步执行
end;
/
-- job 停止(不太好用)
begin
dbms_scheduler.stop_job(job_name => ''COLA_JOB'',force => TRUE);
end;
/
-- job 删除(对停job来说好用)
begin
dbms_scheduler.drop_job(job_name => ''COLA_JOB'',force => TRUE);)
end;
/
本文作者:网友 来源:网络收集