dbms_scheduler job 的简单管理

来源：互联网发布：有为有不为,知足知不足编辑：程序博客网时间：2024/04/27 13:16

还是上一篇的语句，小改下：

BEGIN  dbms_scheduler.drop_job(job_name        => 'scheduler_job_test');END;/TRUNCATE TABLE t_job_test;/TRUNCATE TABLE t_job_log;/BEGIN  dbms_scheduler.purge_log(job_name => 'scheduler_job_test');END;/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_scheduler_job_test', SYSDATE, v_code, v_err);    NULL;END;/BEGIN  dbms_scheduler.create_job(job_name        => 'scheduler_job_test',                            job_type        => 'STORED_PROCEDURE',                            job_action      => 'p_job_test',                            start_date      => SYSDATE,                            repeat_interval => 'FREQ=SECONDLY; INTERVAL=5'/*,                            enabled         => TRUE*/);END;/

我注释了参数 /*, enabled => TRUE*/

这时可以看到

SQL> SELECT state FROM User_Scheduler_Jobs WHERE job_name = 'SCHEDULER_JOB_TEST';STATE---------------DISABLED1 row selected

而这时job是不会执行的，需要用enable启用才可以：

SQL> BEGIN  2    dbms_scheduler.enable('SCHEDULER_JOB_TEST');  3  END;  4  /PL/SQL procedure successfully completedSQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:27:14  dbms_job1 row selectedSQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:27:18  dbms_job2014-08-15 14:27:14  dbms_job2 rows selected

而关闭的命令就是disable

SQL> BEGIN  2    DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB_TEST');  3  END;  4  /PL/SQL procedure successfully completedSQL> select count(*) from t_job_test;  COUNT(*)----------        111 row selectedSQL> /  COUNT(*)----------        111 row selectedSQL> /  COUNT(*)----------        111 row selectedSQL> /  COUNT(*)----------        111 row selectedSQL>

我们还可以修改job的属性

TRUNCATE TABLE t_job_test;/TRUNCATE TABLE t_job_log;/exec dbms_scheduler.set_attribute('SCHEDULER_JOB_TEST','repeat_interval','FREQ=MINUTELY; INTERVAL=1; BYSECOND=0,10,20,30,40,50');BEGIN  dbms_scheduler.enable('SCHEDULER_JOB_TEST');END;/

执行方式有变动:

SQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:31:50  dbms_job2014-08-15 14:31:40  dbms_job2014-08-15 14:32:00  dbms_job3 rows selected

运行期也可以改写

SQL> TRUNCATE TABLE t_job_test;Table truncatedSQL> /Table truncatedSQL> TRUNCATE TABLE t_job_log;Table truncatedSQL> /Table truncatedSQL> exec dbms_scheduler.set_attribute('SCHEDULER_JOB_TEST','repeat_interval','FREQ=MINUTELY; INTERVAL=1; BYSECOND=0,30');PL/SQL procedure successfully completedSQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:33:00  dbms_job2014-08-15 14:33:30  dbms_job2 rows selectedSQL>

需要注意，stop_job命令是停止正在运行的job，如果执行时该job处理间隔期，则stop_job会报错

SQL> TRUNCATE TABLE t_job_test;Table truncatedSQL> /Table truncatedSQL> TRUNCATE TABLE t_job_log;Table truncatedSQL> /Table truncatedSQL> exec dbms_scheduler.stop_job('SCHEDULER_JOB_TEST');begin dbms_scheduler.stop_job('SCHEDULER_JOB_TEST'); end;ORA-27366: 作业 "TEST.SCHEDULER_JOB_TEST" 不在运行ORA-06512: 在 "SYS.DBMS_ISCHED", line 210ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 557ORA-06512: 在 line 1SQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:37:30  dbms_job1 row selected

可以看到，job仍在定时运行，但stop_job会报错，因为每次执行时间很短。如果想停止定时，要用disable

TRUNCATE TABLE t_job_test;/TRUNCATE TABLE t_job_log;/BEGIN  DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB_TEST');END;/

如果job未启用或未到执行时间，也可以用run_job直接运行一次：

SQL> exec dbms_scheduler.run_job('SCHEDULER_JOB_TEST');PL/SQL procedure successfully completedSQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:41:06  dbms_job1 row selectedSQL> exec dbms_scheduler.run_job('SCHEDULER_JOB_TEST');PL/SQL procedure successfully completedSQL> select * from t_job_test where rownum <=5;CREATED              DES-------------------- --------------------------------------------------2014-08-15 14:41:06  dbms_job2014-08-15 14:41:22  dbms_job2 rows selectedSQL>

0 0