|
【赛迪网－it技术报道】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;
/
|
