本文于 107 天前发布，最后更新于 6 天前

一、作业系统三要素

• 作业（Job）：要执行的任务，包含一个或多个步骤。
• 调度（Schedule）：定义作业何时执行、执行频率、有效时间范围。
• 警报（Alert）：当作业执行中发生特定事件（如失败）时触发。
• 操作员（Operator）：接收警报通知的人员（通过邮件或网络）。

二、核心系统表（SYSJOB模式）

存储用户定义的作业信息

SELECT * FROM SYSJOB.SYSJOBS;

存储作业包括的所有步骤信息

SELECT * FROM SYSJOB.SYSJOBSTEPS;

存储作业的调度信息

SELECT * FROM SYSJOB.SYSJOBSCHEDULES;

说明：一个作业可以有多个调度，调度用来指定一个作业的执行情况，可以指定作业的执行方式及时间范围。SYSJOBSCHEDULES 表聚集关键字为 JOBID 及调度名，意味着对于一个指定的作业，不能具有同名的调度。

存储作业的执行情况的日志

SELECT * FROM SYSJOB.SYSJOBHISTORIES2;

说明：当一个作业执行完成后，会向这个表中插入一条作业执行情况的记录。
这个表中的所有记录都是由作业在运行过程中系统自动插入的，不是由用户来操作的。

存储作业步骤的执行情况的日志

SELECT * FROM SYSJOB.SYSSTEPHISTORIES2;

三、常用系统过程

• 初始化代理：SP_INIT_JOB_SYS(1)
• 销毁代理：SP_INIT_JOB_SYS(0)
• 创建/修改/删除作业：SP_CREATE_JOB / SP_ALTER_JOB / SP_DROP_JOB
• 开始/提交作业配置：SP_JOB_CONFIG_START / SP_JOB_CONFIG_COMMIT
• 添加/修改/删除步骤：SP_ADD_JOB_STEP / SP_ALTER_JOB_STEP / SP_DROP_JOB_STEP
• 添加/修改/删除调度：SP_ADD_JOB_SCHEDULE / SP_ALTER_JOB_SCHEDULE / SP_DROP_JOB_SCHEDULE
• 清除历史日志：SP_JOB_CLEAR_HISTORIES
• 立即执行作业：SP_DBMS_JOB_RUN

四、权限

• 普通用户无操作作业的权限。
• 授予 ADMIN JOB 权限：GRANT ADMIN JOB TO 用户名;
• 只有 DBA 可以执行 SP_INIT_JOB_SYS。

五、其他常用SQL

（1）查看某存储过程的是哪个作业调用的

SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT::VARCHAR
FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
WHERE A.ID=B.JOB AND B.WHAT LIKE '%call procname%';

（2）查看所有作业信息

--查看所有作业信息
SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT
FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
WHERE A.ID=B.JOB;

或者

-- 查看当前启用的作业及对应调度
SELECT A.NAME, A.ENABLE, B.NAME, B.ENABLE, B.STARTTIME, B.DURING_START_DATE, B.VALID
FROM SYSJOB.SYSJOBS A
JOIN SYSJOB.SYSJOBSCHEDULES B ON A.ID = B.JOBID
WHERE A.ENABLE = 1;

（3）查看所有作业的调度信息

SELECT
    SCHED.ID,
    SCHED.NAME AS SCHEDULE_NAME,          -- 调度名称
    JOB.NAME AS JOB_NAME,                 -- 作业名称
    SCHED.JOBID,
    CASE SCHED.ENABLE
        WHEN 1 THEN '启用'
        WHEN 0 THEN '禁用'
        ELSE '未知(' || TO_CHAR(SCHED.ENABLE) || ')'
    END AS ENABLE,
    CASE SCHED.TYPE
        WHEN 0 THEN '只执行一次'
        WHEN 1 THEN '按天'
        WHEN 2 THEN '按周'
        WHEN 3 THEN '按月(指定日期)'
        WHEN 4 THEN '按月(第一周第几天)'
        WHEN 5 THEN '按月(第二周第几天)'
        WHEN 6 THEN '按月(第三周第几天)'
        WHEN 7 THEN '按月(第四周第几天)'
        WHEN 8 THEN '按月(最后一周第几天)'
        ELSE TO_CHAR(SCHED.TYPE)
    END AS TYPE,
    -- FREQ_INTERVAL
    CASE SCHED.TYPE
        WHEN 0 THEN NULL
        WHEN 1 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每天'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 天'
            END
        WHEN 2 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每周'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 周'
            END
        WHEN 3 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每月(指定日期)'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 个月(指定日期)'
            END
        WHEN 4 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每月(第一周第几天)'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 个月(第一周第几天)'
            END
        WHEN 5 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每月(第二周第几天)'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 个月(第二周第几天)'
            END
        WHEN 6 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每月(第三周第几天)'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 个月(第三周第几天)'
            END
        WHEN 7 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每月(第四周第几天)'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 个月(第四周第几天)'
            END
        WHEN 8 THEN 
            CASE WHEN SCHED.FREQ_INTERVAL = 1 THEN '每月(最后一周第几天)'
                 ELSE '每隔 ' || TO_CHAR(SCHED.FREQ_INTERVAL) || ' 个月(最后一周第几天)'
            END
        ELSE TO_CHAR(SCHED.FREQ_INTERVAL)
    END AS FREQ_INTERVAL,
    -- FREQ_SUB_INTERVAL
    CASE
        WHEN SCHED.FREQ_SUB_INTERVAL IS NULL THEN NULL
        WHEN SCHED.TYPE = 2 THEN
            RTRIM(
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 1) = 1 THEN '周日,' ELSE '' END ||
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 2) = 2 THEN '周一,' ELSE '' END ||
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 4) = 4 THEN '周二,' ELSE '' END ||
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 8) = 8 THEN '周三,' ELSE '' END ||
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 16) = 16 THEN '周四,' ELSE '' END ||
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 32) = 32 THEN '周五,' ELSE '' END ||
                CASE WHEN BITAND(SCHED.FREQ_SUB_INTERVAL, 64) = 64 THEN '周六,' ELSE '' END,
            ',')
        WHEN SCHED.TYPE = 3 THEN TO_CHAR(SCHED.FREQ_SUB_INTERVAL) || '号'
        WHEN SCHED.TYPE IN (4,5,6,7,8) THEN
            CASE SCHED.FREQ_SUB_INTERVAL
                WHEN 1 THEN '周一' WHEN 2 THEN '周二' WHEN 3 THEN '周三'
                WHEN 4 THEN '周四' WHEN 5 THEN '周五' WHEN 6 THEN '周六'
                WHEN 7 THEN '周日' ELSE TO_CHAR(SCHED.FREQ_SUB_INTERVAL)
            END
        ELSE TO_CHAR(SCHED.FREQ_SUB_INTERVAL)
    END AS FREQ_SUB_INTERVAL,
    -- FREQ_MINUTE_INTERVAL
    CASE
        WHEN SCHED.FREQ_MINUTE_INTERVAL = 0 THEN '一天内执行一次'
        WHEN SCHED.FREQ_MINUTE_INTERVAL > 32768 THEN '每 ' || TO_CHAR(SCHED.FREQ_MINUTE_INTERVAL - 32768) || ' 秒'
        ELSE '每 ' || TO_CHAR(SCHED.FREQ_MINUTE_INTERVAL) || ' 分钟'
    END AS FREQ_MINUTE_INTERVAL,
    SCHED.STARTTIME,
    SCHED.ENDTIME,
    SCHED.DURING_START_DATE,
    SCHED.DURING_END_DATE,
    SCHED.SCHNAME,
    SCHED.TRIGNAME,
    CASE SCHED.VALID
        WHEN 'Y' THEN '合法'
        WHEN 'N' THEN '不合法'
        ELSE '未知'
    END AS VALID,
    SCHED.DESCRIBE
FROM SYSJOB.SYSJOBSCHEDULES SCHED
LEFT JOIN SYSJOB.SYSJOBS JOB ON SCHED.JOBID = JOB.ID;

（4）查看作业运行历史信息[作业量大谨慎执行]

SELECT *
FROM SYSJOB.SYSSTEPHISTORIES2 A
WHERE (SELECT COUNT(*)
          FROM SYSJOB.SYSSTEPHISTORIES2 B
         WHERE B.NAME = A.NAME
           AND B.EXEC_ID >= A.EXEC_ID) <= 10
ORDER BY A.START_TIME DESC,A.NAME;

查询最近10次作业执行历史（含执行时间）

SELECT ID, NAME, ERRINFO, START_TIME, END_TIME,
       DATEDIFF(SS, START_TIME, END_TIME) USE_TIME
FROM (
    SELECT A.ID, A.NAME, B.ERRINFO, B.START_TIME, B.END_TIME,
           ROW_NUMBER() OVER(PARTITION BY A.NAME ORDER BY B.START_TIME DESC) RN
    FROM SYSJOB.SYSJOBS A
    JOIN SYSJOB.SYSJOBHISTORIES2 B ON A.NAME = B.NAME
) WHERE RN <= 10;

（5）只查看某个作业的历史信息

SELECT *
FROM SYSJOB.SYSSTEPHISTORIES2 A
WHERE (SELECT COUNT(*)
          FROM SYSJOB.SYSSTEPHISTORIES2 B
         WHERE B.NAME = A.NAME
           AND B.EXEC_ID >= A.EXEC_ID) <= 10 
           AND NAME = '作业名'
ORDER BY A.START_TIME DESC,A.NAME;

查询最近10次作业执行历史（含执行时间）

SELECT ID, NAME, ERRINFO, START_TIME, END_TIME,
       DATEDIFF(SS, START_TIME, END_TIME) USE_TIME
FROM (
    SELECT A.ID, A.NAME, B.ERRINFO, B.START_TIME, B.END_TIME,
           ROW_NUMBER() OVER(PARTITION BY A.NAME ORDER BY B.START_TIME DESC) RN
    FROM SYSJOB.SYSJOBS A
    JOIN SYSJOB.SYSJOBHISTORIES2 B ON A.NAME = B.NAME
    WHERE A.NAME = '作业名称'
) WHERE RN <= 10;

（6）查看某作业的历史耗时

SELECT
    TO_CHAR(A.START_TIME, 'YYYY-MM') AS MONTH,
    AVG(DATEDIFF(SECOND, A.START_TIME, A.END_TIME)) AS AVG_DURATION_SEC,
    MIN(DATEDIFF(SECOND, A.START_TIME, A.END_TIME)) AS MIN_SEC,
    MAX(DATEDIFF(SECOND, A.START_TIME, A.END_TIME)) AS MAX_SEC,
    COUNT(*) AS EXEC_COUNT
FROM SYSJOB.SYSSTEPHISTORIES2 A
WHERE A.NAME = 'proc_contracttopay'
GROUP BY TO_CHAR(A.START_TIME, 'YYYY-MM')
ORDER BY MONTH;

（7）批量停止作业

BEGIN FOR JOB_REC IN (
    SELECT
        NAME
    FROM
        SYSJOB.SYSJOBS
) LOOP SP_ENABLE_JOB (JOB_REC.NAME, 0);

-- 0表示禁用
DBMS_OUTPUT.PUT_LINE ('已禁用作业: ' || JOB_REC.NAME);
END LOOP;
END;
/

（8）清理作业历史信息记录

SP_JOB_CLEAR_HISTORIES('JOB_RECORD_BLOCKING');