dbms_job vs Oracle Scheduler

      在oracle 10g之前，我们常使用DBMS_JOB提供的相关软件包来管理oracle数据库中的定时任务，虽然10g之后，更多推荐功能更强大的DBMS_SCHEDULER，但由于习惯问题，很多时候还是会用到DBMS_JOB，下面简单介绍下两种工作的用法。

DBMS_JOB

VARIABLE jobno NUMBER
BEGIN
   DBMS_JOB.SUBMIT(:jobno,
      ‘DBMS_DDL.ANALYZE_OBJECT(‘‘TABLE‘‘,‘‘SYS‘‘, ‘‘TEST‘‘,‘‘ESTIMATE‘‘, NULL, 50);‘,
      SYSDATE, ‘SYSDATE + 1‘);
   COMMIT;
END;
/
sys@MS4ADB3(dtydb5)> PRINT jobno

     JOBNO
----------
         1

sys@MS4ADB3(dtydb5)> select job,what,NEXT_DATE from dba_jobs;

       JOB WHAT                                                                                                 NEXT_DATE
---------- ---------------------------------------------------------------------------------------------------- -------------------
         1 DBMS_DDL.ANALYZE_OBJECT(‘TABLE‘,‘SYS‘, ‘TEST‘,‘ESTIMATE‘, NULL, 50);                                 2014-04-01 14:16:53

‘myproc(‘‘10-JAN-99‘‘, next_date, broken);‘
‘scott.emppackage.give_raise(‘‘JFEE‘‘, 3000.00);‘
‘dbms_job.remove(job);‘

‘SYSDATE + 7‘
每隔7天

‘SYSDATE + 1/48‘
每半个小时

‘NEXT_DAY(TRUNC(SYSDATE), ‘‘MONDAY‘‘) + 15/24‘
每周一下午三点

‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ‘‘Q‘‘), 3), ‘‘THURSDAY‘‘)‘
每季度的第一个周四

BEGIN
DBMS_JOB.REMOVE(14144);
END;
/

BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, ‘SYSDATE + 3‘);
END;
/

BEGIN
DBMS_JOB.WHAT(1,
      ‘DBMS_DDL.ANALYZE_OBJECT(‘‘TABLE‘‘,‘‘SYS‘‘, ‘‘T2‘‘,‘‘ESTIMATE‘‘, NULL, 50);‘);
END;
/

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

BEGIN
DBMS_JOB.RUN(1);
END;
/

sys@MS4ADB3(dtydb5)> SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
  2     FROM DBA_JOBS;

       JOB NEXT_DATE           NEXT_SEC                                                           FAILURES BR
---------- ------------------- ---------------------------------------------------------------- ---------- --
         1 2014-04-01 14:39:39 14:39:39                                                                  0 N    

DBMS_SCHEDULER

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => ‘test_full_job_definition‘,
    job_type        => ‘PLSQL_BLOCK‘,
    job_action      => ‘BEGIN DBMS_STATS.gather_schema_stats(‘‘WJ‘‘); END;‘,
    start_date      => SYSTIMESTAMP,
    repeat_interval => ‘freq=hourly; byminute=0‘,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => ‘Job defined entirely by the CREATE JOB procedure.‘);
END;

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           => ‘my_OS_job‘,
     job_type           => ‘EXECUTABLE‘,
     job_action         => ‘ls /tmp‘,
     repeat_interval    => ‘FREQ=MINUTELY‘,
     enabled            => TRUE
  );
END;

EXEC dbms_scheduler.run_job(‘TEST_FULL_JOB_DEFINITION‘);

SELECT * FROM dba_scheduler_jobs WHERE job_name = ‘TEST_FULL_JOB_DEFINITION‘;

‘FREQ=DAILY; BYDAY=SUN; BYHOUR=18;‘,
 
‘freq=MINUTELY;interval=1‘,  
 
‘freq=MINUTELY;interval=5‘,  
 
‘freq=HOURLY;interval=1‘,    
 
‘FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;‘,

http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13380

参考文档：

http://docs.oracle.com/cd/B10501_01/server.920/a96521/jobq.htm#9499
http://www.mandsconsulting.com/oracle-dbms_scheduler-vs-dbms_job-create-run-monitor-remove
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13380

dbms_job vs Oracle Scheduler,布布扣,bubuko.com

dbms_job vs Oracle Scheduler

原文：http://blog.csdn.net/hijk139/article/details/22669129