Oracle中实现后台自动执行的定时操作(定时器)

1．确保Oracle的工作模式答应启动任务队列治理器:
　　Oracle定时执行“Job Queue”的后台程序是SNP进程，而要启动SNP进程，首先要确保整个系统的模式是可以启动SNP进程的，这需要以DBA的身份去执行如下命令：
　　svrmgrl> alter system enable restricted session;
　　或sql> alter system disenable restricted session;
　　利用如上命令更改系统的会话方式为disenable restricted，为SNP的启动创造条件。
2. Dbms_job.submit的语法为：
   dbms_job.submit( job out binary_integer,
　　whatinarchar2,
　　next_date indate，
　　intervalinvarchar2,
　　no_parseinboolean);
   其中：
　　●job：输出变量，是此任务在任务队列中的编号；
　　●what：执行的任务的名称及其输入参数；
　　●next_date：任务执行的时间；
　　●interval：任务执行的时间间隔。
   将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott, 则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户：
　　svrmgrl> grant execute on dbms_job to scott;
3. 实例：
  Java代码
SQL> declare  
 2  n number;  
 3  begin  
 4  dbms_job.submit(n, 'PKG_RPT_REVERSAL.P_FORM4_REVERSAL_ENTRY;',SYSDATE, null);  
 5  commit;  
 6  end;  
 7  /  
 
L/SQL procedure successfully completed 

 SQL> declare
  2  n number;
  3  begin
  4  dbms_job.submit(n, 'PKG_RPT_REVERSAL.P_FORM4_REVERSAL_ENTRY;',SYSDATE, null);
  5  commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed

PKG_RPT_REVERSAL.P_FORM4_REVERSAL_ENTRY的代码如下：
Java代码
/* 
* insert data into t_prod_tmp table 
*/ 
PROCEDURE P_INSERT_PROD_TMP(I_PROCESS_DATE IN DATE)AS  
  M_START_DATE date;  
  M_END_DATE date;  
  BEGIN  
    select trunc(I_PROCESS_DATE, 'MONTH') into M_START_DATE from dual;  
    M_END_DATE := ADD_MONTHS(M_START_DATE, 1);  
      
   INSERT INTO T_PROD_TMP  
    (POLICY_ID, CHANGE_ID, SERVICE_ID, FINISH_TIME)  
   SELECT PC.POLICY_ID,PC.CHANGE_ID,PC.SERVICE_ID,PC.FINISH_TIME  
   FROM T_POLICY_CHANGE PC, T_ACTUARY_FORM4_TRANSACTION FT  
   WHERE PC.CHANGE_STATUS = '4' ----撤销状态  
   AND PC.SERVICE_ID = FT.TRANS_ID  
   AND FT.TRANS_STATUS = '1' -----有效Service  
   AND PC.FINISH_TIME >= M_START_DATE  
   AND PC.FINISH_TIME < M_END_DATE  
   AND PC.WITHDRAW_TIME <= PC.FINISH_TIME ---Service Undo操作时间应该先于finish_time  
   ORDER BY PC.POLICY_ID,PC.FINISH_TIME;  
     
  END P_INSERT_PROD_TMP;  
  /* 
  * Function Entry  
  */ 
  PROCEDURE P_FORM4_REVERSAL_ENTRY AS  
  BEGIN  
     ---- insert data about service reversal Trans into T_PROD_TMP Table-----  
     P_INSERT_PROD_TMP(SYSDATE);  
     ---some actions on database are as follows...  
  END P_FORM4_REVERSAL_ENTRY;  
end PKG_RPT_REVERSAL; 

/*
* insert data into t_prod_tmp table
*/
PROCEDURE P_INSERT_PROD_TMP(I_PROCESS_DATE IN DATE)AS
  M_START_DATE date;
  M_END_DATE date;
  BEGIN
    select trunc(I_PROCESS_DATE, 'MONTH') into M_START_DATE from dual;
    M_END_DATE := ADD_MONTHS(M_START_DATE, 1);
   
   INSERT INTO T_PROD_TMP
    (POLICY_ID, CHANGE_ID, SERVICE_ID, FINISH_TIME)
   SELECT PC.POLICY_ID,PC.CHANGE_ID,PC.SERVICE_ID,PC.FINISH_TIME
   FROM T_POLICY_CHANGE PC, T_ACTUARY_FORM4_TRANSACTION FT
   WHERE PC.CHANGE_STATUS = '4' ----撤销状态
   AND PC.SERVICE_ID = FT.TRANS_ID
   AND FT.TRANS_STATUS = '1' -----有效Service
   AND PC.FINISH_TIME >= M_START_DATE
   AND PC.FINISH_TIME < M_END_DATE
   AND PC.WITHDRAW_TIME <= PC.FINISH_TIME ---Service Undo操作时间应该先于finish_time
   ORDER BY PC.POLICY_ID,PC.FINISH_TIME;
  
  END P_INSERT_PROD_TMP;
  /*
  * Function Entry
  */
  PROCEDURE P_FORM4_REVERSAL_ENTRY AS
  BEGIN
     ---- insert data about service reversal Trans into T_PROD_TMP Table-----
     P_INSERT_PROD_TMP(SYSDATE);
     ---some actions on database are as follows...
  END P_FORM4_REVERSAL_ENTRY;
end PKG_RPT_REVERSAL;

查看一下job执行情况：
SQL> select job,next_date,next_sec,failures,broken from user_jobs;

       JOB NEXT_DATE   NEXT_SEC                   FAILURES BROKEN
---------- ----------- ------------------------ ---------- ------
      1726 1/1/4000    00:00:00                            N
      2103 1/1/4000    00:00:00                            N
      2601 1/1/4000    00:00:00                            N
      4021 12/1/2009   00:00:00                          0 N
      6227 11/30/2009  13:22:22                          0 N
      6228 11/30/2009  13:22:17                          0 N
       973 1/1/4000    00:00:00                         17 Y
      2977 12/7/2009   00:00:00                          0 N
      2976 12/7/2009   00:00:00                          0 N
      3579 1/1/4000    00:00:00                         16 Y
     13063 12/1/2009   00:00:00                          0 N
     13149 12/1/2009   00:00:00                          0 N
     19683 11/30/2009  13:22:22                          0 N
     21929 11/30/2009  13:22:17                          0 N
     21930 11/30/2009  13:22:22                          0 N
如果事务到预定时间并没有执行，排除是过程本身的问题外，可能的原因是初始化参数job_queue_processes值为0或没有设置。用alter system set job_queue_processes=n;（n>0）做好设置。注：job_queue_processes最大值为1000