傻逼 Oracle 支持计划任务（job)，可以定时执行存储过程等

使用傻逼 Oracle 内置的 dbms_scheduler 表空间 (table space)里的存储过程 CREATE_JOB, DROP_JOB, RUN_JOB 等来管理 jobs.

Create a job

(PL / SQL 调用 DBMS_SCHEDULER.create_job 存储过程)

begin
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'TESTP;', -- procedure name
    start_date      => SYSTIMESTAMP,
    --number_of_arguments  => 2,
    enabled         => TRUE,
    repeat_interval => 'freq=HOURLY');
end;

• freq 取值: "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" 等。

Job Procedure In parameters

如果定时器调用的存储过程需要(指定)传入参数，需要在 create_job 时，传入 number_of_arguments 指定所需参数个数，

然后用 set_job_anydata_value 修改某个特定参数值。

dbms_scheduler.set_job_anydata_value 
(
  job_name => v_job_name,
  argument_position => 1, -- start from 1
  argument_value => xxx
); 

List Jobs

(System views)

select * from ALL_SCHEDULER_JOBS -- 当前用户有权访问的 jobs
select * from DBA_SCHEDULER_JOBS -- 整个 db 所有 jobs
select * from USER_SCHEDULER_JOBS -- 当前用户所拥有的 jobs,这个 view 没有 "OWNER" 字段

View Fields:

• ENABLED: TRUE|FALSE

Drop Job

The following statement drops jobs job1 and job3, and all jobs in job classes jobclass1 and jobclass2:

BEGIN
  DBMS_SCHEDULER.DROP_JOB ('job1, job3, sys.jobclass1, sys.jobclass2');
END;

View scheduler logs

select * from ALL_SCHEDULER_JOB_RUN_DETAILS
select * from DBA_SCHEDULER_JOB_RUN_DETAILS
select * from USER_SCHEDULER_JOB_RUN_DETAILS