-- 查看系统定时任务
SELECT * FROM DBA_JOBS
-- 新建定时任务
DECLARE
jobno NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(
JOB => jobno, /*自动生成JOB_ID*/
WHAT => 'qmcb_ls_data;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => SYSDATE, /*初次执行时间-立即执行*/
INTERVAL => 'TRUNC(LAST_DAY(SYSDATE))+23/24' /*每月最后一天晚9点*/
);
COMMIT;
END;
-- 启动/停止定时任务,next_date是4000-1-1表示这个脚本已经是停止状态
BEGIN
DBMS_JOB.BROKEN(4682, TRUE, SYSDATE); --停止
-- DBMS_JOB.RUN(4682); 启动
-- DBMS_JOB.REMOVE(4682); 删除
COMMIT;
END;
定时任务打包
create or replace package body pkg_tools is
procedure trun_ftplog IS
out_count INTEGER :=0;
v_sql VARCHAR2(1024);
BEGIN
SELECT COUNT(1) into out_count FROM ETL_JOB_INST WHERE state='10D';
IF out_count=0 then
v_sql := 'TRUNCATE TABLE ETL_CPT_FTP_LOG';
EXECUTE IMMEDIATE v_sql;
END IF;
end trun_ftplog;
end pkg_tools;
参考文档:
来源:https://www.cnblogs.com/iupoint/p/10824125.html