Oracle 10g schedule job的常用操作

  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;

/