DBMS_JOB

DBMS_SCHEDULER 出来很多年了,还有人在用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> 



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值