相关系统视图

SELECT * FROM SYSJOB.SYSJOBS;
SELECT * FROM SYSJOB.SYSJOBHISTORIES2;
SELECT * FROM SYSJOB.SYSJOBSCHEDULES;
SELECT * FROM SYSJOB.SYSJOBSTEPS;

查看所有作业信息

--查看所有作业信息
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 * FROM SYSJOB.SYSJOBSCHEDULES;

查看作业运行历史信息

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;

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

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;

批量停止作业

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;
/