DBMS_SCHEDULER 出来很多年了,还有人在用DBMS_JOB
先来看下dbms_job的实现方法
先来看下dbms_job的实现方法
DROP TABLE t_job_log PURGE;
DROP TABLE t_job_test PURGE;
CREATE TABLE t_job_log(proc VARCHAR2(30),created DATE,CODE NUMBER, message VARCHAR2(500));
CREATE TABLE t_job_test(created DATE,des VARCHAR2(50));
CREATE OR REPLACE PROCEDURE p_job_test AS
v_code NUMBER;
v_err VARCHAR2(500);
BEGIN
INSERT INTO t_job_test VALUES (SYSDATE, 'dbms_job');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_err := substrb(SQLERRM, 1, 500);
INSERT INTO t_job_log VALUES ('p_job_test', SYSDATE, v_code, v_err);
NULL;
END;
/
SET serveroutput ON
DECLARE
v_job BINARY_INTEGER;
BEGIN
dbms_job.submit(job => v_job,
what => 'begin p_job_test; end;',
next_date => SYSDATE,
INTERVAL => 'sysdate + 5/24/60/60');
dbms_output.put_line(v_job);
END;
/
commit;
执行结果:SQL> select * from t_job_test where rownum <=5;
CREATED DES
-------------------- --------------------------------------------------
2014-08-15 11:26:48 dbms_job
2014-08-15 11:27:23 dbms_job
2014-08-15 11:27:38 dbms_job
2014-08-15 11:27:53 dbms_job
2014-08-15 11:27:58 dbms_job
5 rows selected
相关监控信息比较少
SQL> select JOB,WHAT,LAST_DATE,THIS_DATE from user_jobs;
JOB WHAT LAST_DATE THIS_DATE
---------- ------------------------------ ----------- -----------
5 begin p_job_test; end; 2014-08-15
1 row selected
删掉job
BEGIN
dbms_job.broken(job => 5,broken => TRUE);
dbms_job.remove(job => 5);
COMMIT;
END;
/
SQL> select JOB,WHAT,LAST_DATE,THIS_DATE from user_jobs;
JOB WHAT LAST_DATE THIS_DATE
---------- ------------------------------ ----------- -----------
SQL>