Oracle 定時器（Job）實例

1.
create table anrry(time1 date);

2. 
create  procedure test520 
 as    
 begin
    insert into anrry values(sysdate);
 end;

3.
 declare   
    jobno number;   
    
begin
  sys.dbms_job.submit(job => jobno,
                     /* what => 'DECLARE BEGIN
                                        delete from wst_leave_factory_bill_det_t  t
                                       where 1=1  and t.creation_date>sysdate-7 and t.serial_no1 is null;
                                       COMMIT;
                                       END;',*/
                      what => 'test520'
                      next_date => sysdate,--to_date('20-05-2014 14:05:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+1/1440');
  commit;
end;


4.
select  to_char(time1,'yyyy/mm/dd hh24:mi:ss') time  from anrry;


 select * from user_jobs --可以查到所有job
where 1=1
and what like 'test520%'

select * from dba_jobs_running--可以查到所有正在running 的job

select *                                                     
from v$session 
--where action like '%HAPPY%'
--where module='XXMPM013'                                                    
where sid ='579'   

alter system kill session '579,1674'; 

當時做這個實例的時候發現沒有執行成功，語句沒有問題，原因是某個其他的有兩個session lock了，通過

select * from dba_jobs_running

查到有兩條語句一直在running ，而

job_queue_process 設置為2.即一次只能running 2個，所以改為10 之後，就成功了。

SQL> show parameter job_queue_process;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     2
 
SQL> ALTER  SYSTEM  SET  job_queue_processes = 10;
 
System altered
 
SQL> show parameter job_queue_process;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10