Oracle作业调度器Job Scheduler

Oracle数据库调度器 (Oracle Database Scheduler)

• 在数据库管理系统中，数据库调度器负责调度和执行数据库中的存储过程、触发器、事件等。
• 它可以确保这些操作在正确的时间和条件下得到执行，以满足业务需求。

1、授权用户权限

-- 创建目录对象 tmp_dir 
CREATE OR REPLACE DIRECTORY tmp_dir AS '/tmp';
-- 授予用户权限
GRANT READ,WRITE ON DIRECTORY tmp_dir TO hr;
GRANT CREATE JOB TO hr;
GRANT CREATE EXTERNAL JOB TO hr;

2、创建一个测试表

创建一个测试表 t_job_oggdata

conn hr/hr@orcl

CREATE TABLE t_job_oggdata
(  scheduler_ID NUMBER CONSTRAINT PK_t_job_oggdata_ID PRIMARY KEY,
   Infoment VARCHAR2(50),
   sysguid VARCHAR(50) DEFAULT sys_guid(), 
   createDT DATE DEFAULT SYSDATE,
   CONSTRAINT UQ_uniqueKeyguid UNIQUE(sysguid)
);

3、创建序列SEQUENCE及存储过程PROCEDURE

创建序列SEQUENCE SEQ_t_job_oggdataID 及存储过程PROCEDURE P_TestSchedulerJobs

-- 创建序列SEQUENCE SEQ_t_job_oggdataID
CREATE SEQUENCE SEQ_t_job_oggdataID MINVALUE 1 MAXVALUE 999999999999 START WITH  1 CYCLE NOCACHE;

-- 创建存储过程PROCEDURE  P_TestSchedulerJobs
CREATE OR REPLACE PROCEDURE P_TestSchedulerJobs
AS
BEGIN
  INSERT INTO t_job_oggdata(scheduler_ID,Infoment) 
  VALUES (SEQ_t_job_oggdataID.nextval,SEQ_t_job_oggdataID.currval||'_scheduler_jobs_ID ');
  COMMIT;
END P_TestSchedulerJobs;
/

4、创建一个 SCHEDULER Job(创建 JOb 默认不运行)

创建一个 SCHEDULER Job，时间间隔为每分钟执行一次

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => 'AddSchedulerJobs',
                            JOB_TYPE        => 'stored_procedure',
                            JOB_ACTION      => 'P_TestSchedulerJobs',
                            START_DATE      => SYSDATE,
                            REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1');
END;
/

5、查询 scheduler JOB

-- sys用户查
select * from dba_scheduler_jobs where owner=UPPER('sys') AND job_name=UPPER('AddSchedulerJobs');

-- hr用户查
select * from user_scheduler_jobs where job_name=UPPER('AddSchedulerJobs');

select * from t_job_oggdata;

6、使用 scheduler 管理 JOB

6.1、启动 JOB

begin
    dbms_scheduler.enable('AddSchedulerJobs');
end;
/

PL/SQL procedure successfully completed.

-- hr用户下查user_scheduler_jobs 
HR@ORCL> col JOB_NAME format a30
HR@ORCL> select JOB_NAME,ENABLED from user_scheduler_jobs where job_name=UPPER('AddSchedulerJobs');

JOB_NAME                       ENABLED
------------------------------ ---------------
ADDSCHEDULERJOBS               TRUE

-- sys用户下查dba_scheduler_jobs 
select JOB_NAME,ENABLED from dba_scheduler_jobs where owner=UPPER('hr') AND job_name=UPPER('AddSchedulerJobs');

HR@ORCL> col INFOMENT format a30
HR@ORCL> col SYSGUID format a36
HR@ORCL> select * from t_job_oggdata;

SCHEDULER_ID INFOMENT                       SYSGUID                              CREATEDT
------------ ------------------------------ ------------------------------------ -------------------
           1 1_scheduler_jobs_ID            1A0D38A2BDFCD321E063E650A8C0EE7F     2024-06-04 16:27:32
           2 2_scheduler_jobs_ID            1A0D38A2BDFDD321E063E650A8C0EE7F     2024-06-04 16:28:32
           3 3_scheduler_jobs_ID            1A0D3FCA1BDCD323E063E650A8C00246     2024-06-04 16:29:32
           4 4_scheduler_jobs_ID            1A0D38A2BDFED321E063E650A8C0EE7F     2024-06-04 16:30:32

查看启动时间和下次执行时间

--  sys as sysdba 
select JOB_NAME,ENABLED,
       to_char(START_DATE,'yyyy-mm-dd hh24:mi:ss') as start_date,
       to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') as next_run_date
from dba_scheduler_jobs where owner=UPPER('hr') AND job_name=UPPER('AddSchedulerJobs');

-- OR schema hr
select JOB_NAME,ENABLED,
       to_char(START_DATE,'yyyy-mm-dd hh24:mi:ss') as start_date,
       to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') as next_run_date
from user_scheduler_jobs where job_name=UPPER('AddSchedulerJobs');

查看执行的详细信息

HR@ORCL> col LOG_DATE format a30
HR@ORCL> col STATUS format a10
HR@ORCL> col ACTUAL_START_DATE format a30
HR@ORCL> col ADDITIONAL_INFO format a50
HR@ORCL> col RUN_DURATION format a30
HR@ORCL> select owner,job_name,to_char(log_date,'yyyy-mm-dd hh24:mi:ss') as log_date,
                status,RUN_DURATION,to_char(ACTUAL_START_DATE,'yyyy-mm-dd hh24:mi:ss') as actual_start_date,
                ADDITIONAL_INFO
         from user_scheduler_job_run_details;

OWNER                          JOB_NAME                       LOG_DATE                       STATUS     RUN_DURATION                   ACTUAL_START_DATE              ADDITIONAL_INFO
------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ --------------------------------------------------
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:27:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:27:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:28:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:28:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:30:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:30:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:31:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:31:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:32:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:32:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:33:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:33:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:29:32            SUCCEEDED  +000 00:00:00                  2024-06-04 16:29:32
TESTUSER                       ADDSCHEDULERJOBS               2024-06-04 16:36:48            SUCCEEDED  +000 00:00:00                  2024-06-04 16:36:48

8 rows selected.

6.2、禁用 JOB

begin
  dbms_scheduler.disable('AddSchedulerJobs');
end;
/

PL/SQL procedure successfully completed.

6.3、执行 JOB

-- 查询测试表中的数据
HR@ORCL> select * from t_job_oggdata;

SCHEDULER_ID INFOMENT                       SYSGUID                              CREATEDT
------------ ------------------------------ ------------------------------------ -------------------
           1 1_scheduler_jobs_ID            1A0D38A2BDFCD321E063E650A8C0EE7F     2024-06-04 16:27:32
           2 2_scheduler_jobs_ID            1A0D38A2BDFDD321E063E650A8C0EE7F     2024-06-04 16:28:32
           3 3_scheduler_jobs_ID            1A0D3FCA1BDCD323E063E650A8C00246     2024-06-04 16:29:32
           4 4_scheduler_jobs_ID            1A0D38A2BDFED321E063E650A8C0EE7F     2024-06-04 16:30:32
           5 5_scheduler_jobs_ID            1A0D38A2BDFFD321E063E650A8C0EE7F     2024-06-04 16:31:32
           6 6_scheduler_jobs_ID            1A0D38A2BE00D321E063E650A8C0EE7F     2024-06-04 16:32:32
           7 7_scheduler_jobs_ID            1A0D38A2BE01D321E063E650A8C0EE7F     2024-06-04 16:33:32

7 rows selected.

-- 执行 JOB
BEGIN
  DBMS_SCHEDULER.RUN_JOB('AddSchedulerJobs');
END;
/

PL/SQL procedure successfully completed.

-- 查询测试表中的数据
HR@ORCL> select * from test_scheduler_job;

SCHEDULER_ID INFOMENT                       SYSGUID                              CREATEDT
------------ ------------------------------ ------------------------------------ -------------------
           1 1_scheduler_jobs_ID            1A0D38A2BDFCD321E063E650A8C0EE7F     2024-06-04 16:27:32
           2 2_scheduler_jobs_ID            1A0D38A2BDFDD321E063E650A8C0EE7F     2024-06-04 16:28:32
           3 3_scheduler_jobs_ID            1A0D3FCA1BDCD323E063E650A8C00246     2024-06-04 16:29:32
           4 4_scheduler_jobs_ID            1A0D38A2BDFED321E063E650A8C0EE7F     2024-06-04 16:30:32
           5 5_scheduler_jobs_ID            1A0D38A2BDFFD321E063E650A8C0EE7F     2024-06-04 16:31:32
           6 6_scheduler_jobs_ID            1A0D38A2BE00D321E063E650A8C0EE7F     2024-06-04 16:32:32
           7 7_scheduler_jobs_ID            1A0D38A2BE01D321E063E650A8C0EE7F     2024-06-04 16:33:32
           8 8_scheduler_jobs_ID            1A0D1928BA26D2ACE063E650A8C04D90     2024-06-04 16:36:48

8 rows selected.

在许多应用程序和系统中，开发人员可能会根据特定需求创建自定义的调度器来管理和调度任务。这些自定义调度器可以根据应用程序的特定逻辑和规则来选择自动或手动执行。

6.4、删除 JOB

BEGIN
  DBMS_SCHEDULER.DROP_JOB('AddSchedulerJobs');
END;
/

再次查询user_scheduler_jobs ，发现已经没有这个 scheduler jobs

HR@ORCL> select JOB_NAME,ENABLED from user_scheduler_jobs where job_name=UPPER('AddSchedulerJobs');

no rows selected

7、JOB 的时间使用总结

7.1、关于 job 运行时间 计算方法：

select TRUNC(sysdate) + 1 +2/(24) from dual;

7.2、每分钟执行

Interval => TRUNC(sysdate,'mi') + 1/(24*60)

7.3、每天定时执行

每天的凌晨 1 点执行

Interval => TRUNC(sysdate) + 1 +1/(24)

每天的凌晨 2 点执行

Interval => TRUNC(sysdate) + 1 +2/(24)

当前时间间隔2 天

Interval => TRUNC(sysdate) + 2 +1/(24)

7.4、每周定时执行

例如：每周一凌晨 1 点执行

Interval => TRUNC(next_day(sysdate,'Mon'))+1/24

7.5、每月定时执行

例如：每月 1 日凌晨 1 点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

7.6、每季度定时执行

例如每季度的第一天凌晨 1 点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

7.7、每半年定时执行

例如：每年 7 月 1 日和 1 月 1 日凌晨 1 点

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

Interval => TRUNC(SYSDATE, 'YEAR')  + 1/24