create or replace procedure aatestproc as begin insert into aatest (val) values(1); commit; end; --上面创建一个过程SQL> begin 2 dbms_job.submit(:aatestjob,'aatestproc;',sysdate,'sysdate+1/1440'); 3 end; 4 / 一天1440分钟,每分钟执行一次PL/SQL procedure successfully completed aatestjob --------- 1 SQL> begin 2 dbms_job.run(:aatestjob); 3 end; 4 /PL/SQL procedure successfully completed aatestjob --------- 1SQL> select * from aatest; VAL UPDATEDATE --------------------------------------- ----------- 1 2003-10-27 1 2003-10-27 trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); 这个job 任务定义了收集数据的时间间隔: 一天有24 个小时,1440 分钟,那么: 1/24 HH 每小时一次 1/48 MI 每半小时一次 1/144 MI 每十分钟一次 1/288 MI 每五分钟一次 我们可以修改spauto.sql 来更改执行间隔,如: dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno); 这样我们就建立了一个每30 分钟执行一次
用 JOB 的方式就可以了.
用Job来解决: Create Or Replace Procedure Auto_Arch_Job(v_timestr In Varchar2,v_jobno Out Varchar2,v_msg Out Varchar2) Is /****************************************************************************** PURPOSE: 自动存档Job的更改与建立 REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2003-10-17 Fred Zhang 1. Created this procedure.******************************************************************************/ jobno Integer; jobid Integer; Begin Select job Into jobid From all_jobs Where what='AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;'; dbms_job.Change(jobid,'AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1'); Commit; v_jobno:=jobid; v_msg:='恭喜你!存档数据自动导入设定已经成功更改!'; Exception When No_Data_Found Then dbms_job.submit(jobid,'AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1'); Commit; v_jobno:=jobid; v_msg:='恭喜你!存档数据自动导入已经成功设定!'; End Auto_Arch_Job; /
SQL> create table aa(a number,b date);Table createdSQL> SQL> create or replace procedure pro 2 is 3 begin 4 insert into aa values(1,sysdate); 5 end; 6 /Procedure createdQL> declare 2 jobno number; 3 begin 4 dbms_job.submit(jobno,'pro;',sysdate,'sysdate+1/(24*60)'); 5 commit; 6 end; 7 /
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间:dbms_job.next_date(job,next_date);
修改间隔时间:dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;', --存储过程名
SYSDATE, 'SYSDATE + 1/720');---每2分钟执行一次
commit;
end;
/
第二种是利用操作系统提供的功能,如unix的cron,windows的task schedule,调用一个脚本文件,在脚本文件用sqlplus中连接至oracle数据库,进行各种操作.
as
begin
insert into aatest (val) values(1);
commit;
end;
--上面创建一个过程SQL> begin
2 dbms_job.submit(:aatestjob,'aatestproc;',sysdate,'sysdate+1/1440');
3 end;
4 /
一天1440分钟,每分钟执行一次PL/SQL procedure successfully completed
aatestjob
---------
1
SQL> begin
2 dbms_job.run(:aatestjob);
3 end;
4 /PL/SQL procedure successfully completed
aatestjob
---------
1SQL> select * from aatest; VAL UPDATEDATE
--------------------------------------- -----------
1 2003-10-27
1 2003-10-27
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
这个job 任务定义了收集数据的时间间隔:
一天有24 个小时,1440 分钟,那么:
1/24 HH 每小时一次
1/48 MI 每半小时一次
1/144 MI 每十分钟一次
1/288 MI 每五分钟一次
我们可以修改spauto.sql 来更改执行间隔,如:
dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
这样我们就建立了一个每30 分钟执行一次
Create Or Replace Procedure Auto_Arch_Job(v_timestr In Varchar2,v_jobno Out Varchar2,v_msg Out Varchar2) Is
/******************************************************************************
PURPOSE: 自动存档Job的更改与建立 REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2003-10-17 Fred Zhang 1. Created this procedure.******************************************************************************/
jobno Integer;
jobid Integer;
Begin
Select job Into jobid From all_jobs Where what='AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;';
dbms_job.Change(jobid,'AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1');
Commit;
v_jobno:=jobid;
v_msg:='恭喜你!存档数据自动导入设定已经成功更改!';
Exception When No_Data_Found Then
dbms_job.submit(jobid,'AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1');
Commit;
v_jobno:=jobid;
v_msg:='恭喜你!存档数据自动导入已经成功设定!';
End Auto_Arch_Job;
/
SQL> create or replace procedure pro
2 is
3 begin
4 insert into aa values(1,sysdate);
5 end;
6 /Procedure createdQL> declare
2 jobno number;
3 begin
4 dbms_job.submit(jobno,'pro;',sysdate,'sysdate+1/(24*60)');
5 commit;
6 end;
7 /