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