Oracle 工作排程 DBMS_JOB 筆記

1. 設定初始化參數

job_queue_processes : oracle 允許執行的最大任務數量

specifies the maximum number of processes that can be
created for the execution of jobs. It specifies the number of job queue processes per
instance (J000, ... J999).

最大值為 1000

sql> alter system set job_queue_processes=n; (n>0)

2. 查詢背景執行程序

sql> select name, description from v$bgprocess

3. 建立function or Stored Procedure

4. 執行程序

declare
  jobno    number;
begin
  dbms_job.submit(jobno, 'sp_test_alex;',  sysdate, 'sysdate+1/1440');
  dbms_output.put_line(jobno);
  commit;
end;
/

另一種寫法

declare
  jobno         number;
begin
  dbms_job.submit(
    job => :jobno,
    what => 'SP_SYNC_MES_ITEM;',
    next_date => SYSDATE,
    interval => 'SYSDATE + 10/1440'
  );

  dbms_output.put_line(jobno);
  commit;
end;

5. 查詢執行程序

sql> select * from user_jobs;

6.  移除程序

begin
dbms_job.remove(141);
end;
/

7. 暫停程序

begin
  dbms_job.broken([jobs: 程序#], [broken: true/false], [next_date: 下次一執行日期時間]);
end;
/

next_date未指定時, 預設為目前的日期時間

begin
  dbms_job.broken(158, true);
end;
/

8. 恢復執行程序

begin
  dbms_job.broken(158, false, sysdate);
end;
/

9. 強制執行程序(不論是否已設定broken)

begin
  dbms_job.run(158, true);
end;
/

//2014/07/28 Alex add 透過修改, job no不會一直變動, 若是透過移除重建, job no則會一直變更及增加
10. 修改排程的設定

DBMS_JOB.CHANGE ( 
   job       IN  BINARY_INTEGER,
   what      IN  VARCHAR2,
   next_date IN  DATE,
   interval  IN  VARCHAR2,
   instance  IN  BINARY_INTEGER DEFAULT NULL,
   force     IN  BOOLEAN DEFAULT FALSE);

begin  dbms_job.Change(
 5,
 'SP_ORDER_INFO;',
 to_date(to_char(sysdate, 'yyyy/mm/dd') || ' 07:50:00', 'yyyy/mm/dd hh24:mi:ss'),
 'to_date(to_char(sysdate, ''yyyy/mm/dd'') || '' 07:50:00'', ''yyyy/mm/dd hh24:mi:ss'')+1'
);
end;
/

ref web:

http://oracle.chinaitlab.com/optimize/758621.html
http://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/dbms_job.htm#1001069