-- 创建定时任务,判断如果已有这个定时任务,则删除重建
DECLARE JobNum NUMBER(5) := 0;
JobID NUMBER(5) := 0;
BEGIN
-- 查是否已经存在,调度的job
SELECT COUNT(*) INTO JobNum FROM DBA_JOBS WHERE UPPER(WHAT) LIke UPPER('SP_MON_DATA') ||'%';
-- 存在就删除
IF JobNum > 0 THEN
SELECT NVL(JOB,0) INTO JobID FROM DBA_JOBS WHERE UPPER(WHAT) LIke UPPER('SSP_MON_DATA') ||'%';
IF JobID > 0 THEN
DBMS_JOB.REMOVE(JobID);
COMMIT;
END IF;
END IF;
END;
/
DECLARE
v_Job NUMBER;
BEGIN
--SP_MON_DATA 为存储过程(没有参数),SYSDATE + 1 下个粒度
DBMS_JOB.SUBMIT(v_Job, 'SP_MON_DATA;', TRUNC(SYSDATE, 'HH24') + 1, 'SYSDATE + 1');
COMMIT;
END;
/