Oracle之JOB

DBMS_JOB Package


1 提交作业 submit
dbms_job.submit(job,what,next_date,interval,no_parse);


variable jobno number
begin
dbms_job.submit(:jobno,
'dbms_ddl.analyze_object(''TABLE'',''TEST'',''EMPLOYEES'',''ESTIMATE'',NULL, 50);',
sysdate,-to_date('','');
'sysdate+1');
commit; --necessary
        SYS.DBMS_OUTPUT.put_line ('Job Number is: ' || TO_CHAR (x)); 
end;
/
print jobno


用两个单引号来引用WHAT中的变量,而不是是双引号
what中语句的分号不可少.


2 删除作业 remove


BEGIN
DBMS_JOB.REMOVE(1);
END;
/


3 更改作业 Alter


BEGIN
DBMS_JOB.CHANGE(1, NULL, NULL, 'SYSDATE + 3');
END;
/
NULL 表示不做更改,保留当前值


BEGIN
DBMS_JOB.WHAT(1,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''test'', ''t2'',''ESTIMATE'', NULL, 50);');
END;
/


BEGIN
DBMS_JOB.NEXT_DATE(1, SYSDATE + 4);
END;
/


BEGIN
DBMS_JOB.INTERVAL(1, 'NULL');
END;
/


每隔半小时
BEGIN
DBMS_JOB.INTERVAL(17,'trunc(SYSDATE+1/48,''MI'')');
END;
/
注意每个都是单引号不是双引号
一天有24个小时,1440分钟,那么:
1/24 HH 每小时一次
1/48 MI 每半小时一次
1/144 MI 每十分钟一次
1/288 MI 第五分钟一次










4 暂停作业 Broken


BEGIN
DBMS_JOB.BROKEN(1, TRUE);
END;
/
exec DBMS_JOB.BROKEN(1, TRUE);


BEGIN
DBMS_JOB.BROKEN(1, FALSE, NEXT_DAY(SYSDATE, 'SUNDAY'));
END;
/


exec DBMS_JOB.BROKEN(61, FALSE, NEXT_DAY(SYSDATE, 'SUNDAY'));
exec DBMS_JOB.BROKEN(:jobno, FALSE,to_date('2013-09-25 16:50:00','yyyy-mm-dd hh24:mi:ss'));


5 强制执行作业 Run


BEGIN
DBMS_JOB.RUN(1);
END;
/


6 查看作业 View
6.1 DBA_JOBS
select job, schema_user, what,last_date,last_sec,next_date,next_sec,interval,broken from dba_jobs;


set linesize 200
col SCHEMA_USER format a15
col what format a30
col next_date format a20
col interval format a30
select SCHEMA_USER,what,to_char(next_date,'yyyymmddhh24miss'),interval,broken from dba_jobs order by SCHEMA_USER;


     
     


6.2 DBA_JOBS_RUNNING


select * from DBA_JOBS_RUNNING;




7.使用trunc等函数精确指定job的间隔
新建一个job，时间间隔为1分钟
(1) 不使用trunc精确定时
variable jobno number
begin
dbms_job.submit(:jobno,
'updateeposterm;',
sysdate,
'sysdate+1/1440');
commit;
end;
/
select job, schema_user, what,last_date,last_sec,next_date,next_sec,interval,broken from dba_jobs where what='updateeposterm;';


       JOB SCHEMA_USER
---------- ------------------------------
WHAT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_DATE LAST_SEC                         NEXT_DATE NEXT_SEC                         INTERVAL       B
--------- -------------------------------- --------- -------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
         3 KING
updateeposterm;
13-MAY-13 14:51:24                         13-MAY-13 14:52:24                         sysdate+1/1440       N




       JOB SCHEMA_USER
---------- ------------------------------
WHAT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_DATE LAST_SEC                         NEXT_DATE NEXT_SEC                         INTERVAL       B
--------- -------------------------------- --------- -------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
         3 KING
updateeposterm;
13-MAY-13 14:52:26                         13-MAY-13 14:53:26                         sysdate+1/1440       N




       JOB SCHEMA_USER
---------- ------------------------------
WHAT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_DATE LAST_SEC                         NEXT_DATE NEXT_SEC                         INTERVAL       B
--------- -------------------------------- --------- -------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
         3 KING
updateeposterm;
13-MAY-13 14:55:27                         13-MAY-13 14:56:27                         sysdate+1/1440       N


实验现象：
可以看到,NEXT_SEC从14:52:24 变为了14:52:26，然后又变为14:56:27 
说明job的下次时间在秒级未精确，会根据执行结束时间计算，如果inverval只使用sysdate，下次执行时间会受job执行时间延后。
因此，interval一定要使用trunc精确指定时间


(2)使用trunc精确定时
exec DBMS_JOB.REMOVE(3);--删除原job


variable jobno number
begin
dbms_job.submit(:jobno,
'updateeposterm;',
sysdate,
'trunc(SYSDATE+1/1440,''MI'')');
commit;
end;
/


SQL> select job, schema_user, what,last_date,last_sec,next_date,next_sec,interval,broken from dba_jobs where what='updateeposterm;';


       JOB SCHEMA_USER
---------- ------------------------------
WHAT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_DATE LAST_SEC                         NEXT_DATE NEXT_SEC                         INTERVAL       B
--------- -------------------------------- --------- -------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
         4 KING
updateeposterm;
13-MAY-13 15:24:04                         13-MAY-13 15:25:00                         trunc(SYSDATE+1/1440,'MI')       N


      JOB SCHEMA_USER
---------- ------------------------------
WHAT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_DATE           LAST_SEC                         NEXT_DATE           NEXT_SEC
------------------- -------------------------------- ------------------- --------------------------------
INTERVAL B
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
         4 KING
updateeposterm;
2013-05-13 17:37:02 17:37:02                         2013-05-13 17:38:00 17:38:00
trunc(SYSDATE+1/1440,'MI') N


       JOB SCHEMA_USER
---------- ------------------------------
WHAT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_DATE           LAST_SEC                         NEXT_DATE           NEXT_SEC
------------------- -------------------------------- ------------------- --------------------------------
INTERVAL B
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
         4 KING
updateeposterm;
2013-05-13 17:46:04 17:46:04                         2013-05-13 17:47:00 17:47:00
trunc(SYSDATE+1/1440,'MI') N




 


实验现象：
可以看见LAST_DATE在秒级的时间（job执行时间)虽然不同，但是每次在00秒时job就会执行
NEXT_DATE是00秒。


结论：job的interval选项必须使用trunc等函数精确指定。


##################################################
Scheduler 与 Job的区别
##################################################
10g开始引入Scheduler


Scheduler 可以由事件或时间触发，而Job只能由时间触发
SCHEDULER 记录历史运行情况，JOB没有


Scheduler 在表达形式上比Job强大多了




grant execute on dbms_scheduler to  test;
exec DBMS_SCHEDULER.CREATE_JOB( job_name => 'test_scheduler', job_type => 'stored_procedure', job_action => 'prc_test_scheduler', start_date => sysdate, repeat_interval => 'FREQ=WEEKLY;INTERVAL=1;BYDAY=SUN;BYHOUR=4;BYMINUTE=0;BYSECOND=0'); 
start_date如何来指定，比如每周日凌晨4点？


create or replace procedure prc_test_scheduler
is
begin
delete from t1 where rownum <11;
commit;
end;
/




execute DBMS_SCHEDULER.enable('test_scheduler'); --启动Job


execute DBMS_SCHEDULER.disable('test_scheduler'); --禁止Job


execute DBMS_SCHEDULER.run_job('test_scheduler'); --立即运行Job


execute DBMS_SCHEDULER.drop_job('test_scheduler'); --删除Job






set linesize 200
col JOB_NAME format a10;
col JOB_TYPE format a10;
col job_action format a15;
col startdate format a15;
col repeat_interval format a25;
select job_name,job_type,job_action,to_char(start_date,'yyyymmddhh24miss') startdate,repeat_interval,enabled,state 
from user_scheduler_jobs
order by job_name;




column owner format a10;
column JOB_NAME format a15;
column log_date format a14;
select OWNER,JOB_NAME,to_char(LOG_DATE,'yyyymmddhh24miss') log_date,STATUS  
from  user_scheduler_job_log 
where job_name=upper('&job_name') 
order by LOG_DATE;