oracle 笔记

dbms_scheduler创建带参数的定时任务

declare
  job number;
begin
  dbms_scheduler.create_job(
    job_name => 'JOB_PROC_HONGHU_UESAGE_DAILY',
    job_type => 'STORED_PROCEDURE',
    job_action => 'PROC_HONGHU_UESAGE_DAILY',
    number_of_arguments => 1,
    repeat_interval => 'FREQ=DAILY;interval=1;BYHOUR=14;BYMINUTE=0',
    enabled =>false
  );
  dbms_scheduler.set_job_argument_value(
    job_name => 'JOB_PROC_HONGHU_UESAGE_DAILY',
    argument_position => 1,
    argument_value => to_char(sysdate - 8/24 -1,'yyyymmdd')
  );
  dbms_scheduler.enable('JOB_PROC_HONGHU_UESAGE_DAILY');
  dbms_scheduler.run_job('JOB_PROC_HONGHU_UESAGE_DAILY');
  end;

dbms_scheduler查看定时任务定义

select * from dba_scheduler_jobs t where t.owner = 'XXX';

dbms_scheduler查看job执行记录

select * from  DBA_scheduler_job_run_details WHERE JOB_NAME = 'JOB_PROC_HONGHU_UESAGE_DAILY';

删除job

--删除
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'TEST_USER_INSERT',FORCE => TRUE);