创建过程
create or replace procedure del_tab is
cursor mycur is
select clbh from xxfl where jgsj<=add_months(sysdate,-1);
begin
for var_clbh in mycur loop
delete from tpxx where tpid=var_clbh.clbh;
delete from xxfl where clbh=var_clbh.clbh;
delete from bjxx where clbh=var_clbh.clbh;
commit;
end loop;
end;alter system set job_queue_processes=10;
打开init.ora,增加或者修改
job_queue_processes=10
重启建立任务
variable job_id number;
begin
dbms_job.submit(:job_id,'del_tab;',trunc(sysdate)+831/1440,'trunc(sysdate)+1');
end;job不能定时执行为什么?
立即执行job是没问题的
select job,last_date,last_sec,broken,FAILURES from user_jobs;
JOB LAST_DATE LAST_SEC B FAILURES
---------- ---------- ---------------- - ----------
21 01-9月 -08 08:50:12 N 0
可是JOB并没有执行啊
create or replace procedure del_tab is
cursor mycur is
select clbh from xxfl where jgsj<=add_months(sysdate,-1);
begin
for var_clbh in mycur loop
delete from tpxx where tpid=var_clbh.clbh;
delete from xxfl where clbh=var_clbh.clbh;
delete from bjxx where clbh=var_clbh.clbh;
commit;
end loop;
end;alter system set job_queue_processes=10;
打开init.ora,增加或者修改
job_queue_processes=10
重启建立任务
variable job_id number;
begin
dbms_job.submit(:job_id,'del_tab;',trunc(sysdate)+831/1440,'trunc(sysdate)+1');
end;job不能定时执行为什么?
立即执行job是没问题的
select job,last_date,last_sec,broken,FAILURES from user_jobs;
JOB LAST_DATE LAST_SEC B FAILURES
---------- ---------- ---------------- - ----------
21 01-9月 -08 08:50:12 N 0
可是JOB并没有执行啊
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'del_tab';
,next_date => to_date('01/09/2008 10:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'trunc(sysdate+1)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/