平时维护job的情况不多,感觉熟练度不够,记录下来,以后查起来也方便。
关键字:oracle job
[@more@]直接用实验例子说明好了。
在用户JOE下执行:
创建表
create table job_xprmt (nowtime date);
创建存储过程
create or replace procedure p_time_record is
begin
insert into job_xprmt values (sysdate);
commit;
end;
/
create or replace procedure p_clear_time_record is
begin
delete job_xprmt where rownum=1;
commit;
end;
/
提交job,也可以认为是创建job
declare
v_job number;
begin
dbms_job.submit(job => v_job,
what => 'BEGIN p_time_record;END;',
next_date => sysdate,
interval => 'SYSDATE + 1/24/60/6');
commit;
end;
/
使用dbms_job包维护job,但要注意一个用户只能维护自己的job,即使是sys也不能通过dbms_job包维护其他用户的job。如有需要维护其他用户的job可以使用dbms_ijob包。
dbms_job包的常用方法如下,注意每个方法执行后都要commit才能生效。
isubmit方法
Submit a new job with a given job number.
可以指定job的值
dbms_job.isubmit(job => 929,
what => 'BEGIN p_time_record;END;',
next_date => sysdate,
interval => 'SYSDATE + 1/24/60/6');
commit;
submit方法
Submit a new job.Chooses JOB from the sequence sys.jobseq.
前面使用过了,job的值会自动分配。
ex.
declare
v_job number;
begin
what => 'BEGIN p_time_record;END;',
next_date => sysdate,
interval => 'SYSDATE + 1/24/60/6');
commit;
end;
/
remove方法
Remove an existing job from the job queue.
删除一个job,可以理解为drop
ex.
execute dbms_job.remove(929);
commit;
change方法
Change any of the the user-settable fields in a job。
修改指定job的参数设置,包括what,next_date,interval
ex.
execute dbms_job.change(929, 'BEGIN p_clear_time_record;END;',’sysdate+1’, 'sysdate+3');
commit;
what方法
Change what an existing job does, and replace its environment.
修改job的what参数。
ex.
exec dbms_job.what(job => 929,what => 'begin p_clear_time_record; end;');
commit;
next_date方法
Change when an existing job will next execute.
修改job的next_date参数
ex.
exec dbms_job.next_date(job => 929,next_date => sysdate+1);
commit;
interval方法
Change how often a job executes
修改job的interval参数
ex.
exec dbms_job.interval(job => 929,interval => sysdate+1/24);
commit;
broken方法
Set the broken flag. Broken jobs are never run.
修改job的broken参数。
broken参数相当于一个开关,broken=true时,job不再运行,broken=false时job恢复运行。
ex.
exec dbms_job.broken(job => 929,broken => true);
exec dbms_job.broken(job => 929,broken => false);
commit;
run方法
Run job JOB now. Run it even if it is broken.
Running the job will recompute next_date.
立刻运行job
ex.
execute dbms_job.run(929);
commit;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21129591/viewspace-1057005/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21129591/viewspace-1057005/