dbms_scheduler job 的简单管理

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

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;
/
           

我注释了参数 

这时可以看到 

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

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

SQL> BEGIN
  2    dbms_scheduler.enable('SCHEDULER_JOB_TEST');
  3  END;
  4  /
PL/SQL procedure successfully completed

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 14:27:14  dbms_job
1 row selected

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 14:27:18  dbms_job
2014-08-15 14:27:14  dbms_job
2 rows selected
           

而关闭的命令就是 disable

SQL> BEGIN
  2    DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB_TEST');
  3  END;
  4  /
PL/SQL procedure successfully completed

SQL> select count(*) from t_job_test;
  COUNT(*)
----------
        11
1 row selected

SQL> /
  COUNT(*)
----------
        11
1 row selected

SQL> /
  COUNT(*)
----------
        11
1 row selected

SQL> /
  COUNT(*)
----------
        11
1 row selected

SQL> 
           

我们还可以修改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_job
2014-08-15 14:31:40  dbms_job
2014-08-15 14:32:00  dbms_job
3 rows selected
           

运行期也可以改写

SQL> TRUNCATE TABLE t_job_test;
Table truncated
SQL> /
Table truncated
SQL> TRUNCATE TABLE t_job_log;
Table truncated
SQL> /
Table truncated
SQL> exec dbms_scheduler.set_attribute('SCHEDULER_JOB_TEST','repeat_interval','FREQ=MINUTELY; INTERVAL=1; BYSECOND=0,30');
PL/SQL procedure successfully completed

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 14:33:00  dbms_job
2014-08-15 14:33:30  dbms_job
2 rows selected

SQL> 
           

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

SQL> TRUNCATE TABLE t_job_test;
Table truncated
SQL> /
Table truncated
SQL> TRUNCATE TABLE t_job_log;
Table truncated
SQL> /
Table truncated
SQL> 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 210
ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 557
ORA-06512: 在 line 1

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 14:37:30  dbms_job
1 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 completed

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 14:41:06  dbms_job
1 row selected

SQL> exec dbms_scheduler.run_job('SCHEDULER_JOB_TEST');
PL/SQL procedure successfully completed

SQL> select * from t_job_test where rownum <=5;
CREATED              DES
-------------------- --------------------------------------------------
2014-08-15 14:41:06  dbms_job
2014-08-15 14:41:22  dbms_job
2 rows selected

SQL>