Oracle 定時任務job實際應用

發布時間：2023-08-18 18:26:57 作者：網友整理

一、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 ,剛才的定時任務又啟動了