PL-SQL筆記6_盤查Oracle Job&Schedule 常用語句

 

盤查JOB的查詢語句，判斷設置JOB執行週期與啟動與否

SELECT JOB_NAME,JOB_CREATOR,JOB_TYPE,JOB_ACTION,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE,START_DATE,REPEAT_INTERVAL,JOB_CLASS,ENABLED,STATE,

JOB_PRIORITY,RUN_COUNT,FAILURE_COUNT,LAST_START_DATE,LAST_RUN_DURATION

FROM USER_SCHEDULER_JOBS

WHERE JOB_NAME='指定你的JOB名稱'

找特定某SP是否被設JOB

SELECT

    j.owner,

    j.job_name,

    j.enabled,

    j.state,

    j.job_type,

    j.job_action,

    j.program_owner,

    j.program_name

FROM ALL_SCHEDULER_JOBS j

WHERE UPPER(j.job_action) LIKE '%你指定的SP名稱%'

   OR EXISTS (

        SELECT 1

        FROM ALL_SCHEDULER_PROGRAMS p

        WHERE p.owner = j.program_owner

          AND p.program_name = j.program_name

          AND UPPER(p.program_action) LIKE '%你指定的SP名稱%'

   )

ORDER BY j.owner, j.job_name;

查找匯出Oracle某支JOB產生的DDL腳本，用於匯出腳本用途

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', '指定你的JOB名稱') FROM DUAL;

查找 Schedule 清單與對應設定

SELECT 

    SCHEDULE_NAME AS "Schedule名稱",

    SCHEDULE_TYPE AS "類型",

    START_DATE AS "開始生效日",

    REPEAT_INTERVAL AS "執行頻率(代表含意)",

    END_DATE AS "結束失效日",

    COMMENTS AS "備註說明"

FROM 

    USER_SCHEDULER_SCHEDULES;

某個 Schedule Name 對應綁定了哪些 Job

SELECT 

    JOB_NAME AS "綁定的JOB名稱",

    JOB_TYPE AS "JOB類型",

    ENABLED AS "是否啟用中",

    STATE AS "目前狀態"

FROM 

    USER_SCHEDULER_JOBS

WHERE 

    SCHEDULE_NAME = '你的_SCHEDULE_名稱';

Values for repeat_interval(calendaring syntax)

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-73622B78-EFF4-4D06-92F5-E358AB2D58F3