Oracle 定時任務job實際應用

xiezhr發表於2023-05-11

一、Oracle定時任務簡介

Oracle定時任務是在oracle系統中一個非常重要的子系統,運用得當,可以大大提高我們系統執行和維護能力。oracle定時任務的功能,可以在指定的時間點自行執行任務。

那麼在實際工作中,什麼樣的場景會用到定時任務呢?下面是在實際工作中用到的真實業務場景舉例

  • 在生成環境中,有時候需要在表中記錄一些業務日誌,系統執行時間長了之後,表中日誌會越來越多,導致系統效能下降,這時候就需要用到定時任務,定時去刪除表中一些時間年代比較久遠垃圾資料。
  • 在某些業務場景中,明細表的資料量特別大,而需要查詢明細表一下彙總資料,就需要將明細表中的資料【某天的業務產生的金額、人數等】透過計算彙總到另外表中,這樣在查詢的時候就能最佳化查詢效率。而以上操作需要在業務量比較少的情況下進行【一般都在凌晨之後】,這是就需要用到定時任務。

二、dbms_job涉及到的知識點

  • 建立job指令碼
variable jobno number;
dbms_job.submit(:jobno,       —-job號 
'your_procedure;',            —-執行的儲存過程, ';'不能省略 
next_date,                    —-下次執行時間 
'interval'                    —-每次間隔時間,
interval                      以天為單位);

上面是透過指令碼建立,當然也可以透過plsql圖形化工具來建立,具體建立過程如下

**系統會自動分配一個任務號jobno ** ,根據jobno 可以進行如下定時任務操作

  • 刪除job: dbms_job.remove(jobno);

  • 修改要執行的操作: job:dbms_job.what(jobno, what);

  • 修改下次執行時間:dbms_job.next_date(jobno, next_date);

  • 修改間隔時間:dbms_job.interval(jobno, interval);

  • 啟動job: dbms_job.run(jobno);

  • 停止job: dbms.broken(jobno, broken, nextdate); –broken為boolean值

三、初始化相關引數job_queue_processes

  • job_queue_process表示oracle能夠併發的job的數量,當job_queue_process值為0時表示全部停止oracle的job
  • 檢視job_queue_processes引數
show parameter job_queue_process;
或者
select * from v$parameter where name='job_queue_processes';
  • 修改job_queue_processes引數
alter system set job_queue_processes = 10;

四、實際建立一個定時任務(一分鐘執行一次),實現定時一分鐘往表中插入資料

4.1 建立需要定時插入資料的目標表

create table t_test (id varchar2(30),
                  name varchar2(30)
                 );

4.2 建立定時執行的儲存過程

create or replace procedure proce_t is
begin
  insert into t_test
    (id, name)
  values
    ('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
  commit;
end proce_t;
/

4.3 建立定時一分鐘定時任務job

variable jobno number;
begin 
dbms_job.submit(:jobno,
'proce_t;',
 sysdate, 
 'sysdate+1/24/60'); 
 commit;
end;

4.5 可以根據以下語句查詢剛建立好的job

select job, next_date, next_sec, failures, broken from user_jobs where job = '1424'

查詢結果如下

SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424'
  2  /
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
      1424 2020-12-30  13:07:14                  0 N

其中broken = N 表示該job已經生效

我們再來檢視目標表中有沒有定時插入資料

SQL> select * from t_test;
 
ID                             NAME
------------------------------ ------------------------------
1                              2020-12-30 13:05:14
1                              2020-12-30 13:03:14
1                              2020-12-30 13:04:14
1                              2020-12-30 13:08:14
1                              2020-12-30 13:06:14
1                              2020-12-30 13:07:14
 
6 rows selected

可以看到,定時一分鐘插入了一條資料。

4.6 如果我們不需要這個定時任務了,那要怎麼停止呢?

1、根據jobno,執行以下指令碼可以停止job

SQL> begin
  2    dbms_job.broken(1424, true, sysdate);
  3    commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed

再來檢視定時任務是否停用成功

SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424';
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
      1424 4000-01-01  00:00:00                  0 Y

我們發現BROKEN=Y 說明定時任務已經停止成功了

4.7 啟用剛才停用的定時任務

SQL> begin
  2   dbms_job.run(1424);
  3   commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed

SQL> select job, next_date, next_sec, failures, broken from user_jobs where job = '1424';
 
       JOB NEXT_DATE   NEXT_SEC           FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
      1424 2020-12-30  13:20:53                  0 N

BROKEN = N ,剛才的定時任務又啟動了

五、定時任務中job執行時間

下面總計了一些定時任務中常用的執行時間

  • 每分鐘執行: TRUNC(sysdate,'mi') + 1/(24*60)

  • 半個小時: sysdate+30/(24*60)

  • 每天凌晨1點執行:TRUNC(sysdate) + 1 +1/(24)

  • 每週一凌晨1點執行: TRUNC(next_day(sysdate,'星期一'))+1/24

  • 每月1日凌晨1點執行: TRUNC(LAST_DAY(SYSDATE))+1+1/24

  • 每季度的第一天凌晨1點執行: TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

  • 每年7月1日和1月1日凌晨1點: ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

  • 每年1月1日凌晨1點執行: ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

  • 每個小時的第15分鐘執行,比如:8:15,9:15,10:15…:trunc(sysdate,'hh')+(60+15)/(24*60)

相關文章