oracle job 用法小结

新建job

dbms_job.submit(
JOB       OUT BINARY_INTEGER,
WHAT      IN  VARCHAR2, 
NEXT_DATE IN  DATE           DEFAULT SYSDATE, 
INTERVAL  IN  VARCHAR2       DEFAULT 'NULL',
NO_PARSE  IN  BOOLEAN        DEFAULT FALSE,
INSTANCE  IN  BINARY_INTEGER DEFAULT 0,
FORCE     IN  BOOLEAN        DEFAULT FALSE);

例如：
declare
  jobnum number;
begin
  dbms_job.submit(jobnum,
                  'procedure1;',
                  trunc(SYSDATE+1)+1/24,
                  'sysdate+1');
end;
/

INTERVAL ：
每天重复执行一次 'SYSDATE + 1'
每周重复执行一次 'SYSDATE + 7'
每小时重复执行一次 'SYSDATE + 1/24'
每十分钟重复执行一次 'SYSDATE + 10/1440'
每30秒重复执行一次 'SYSDATE + 30/86400'
不重复执行 NULL

　　

执行job

EXEC dbms_job.run(175);

 

中止、启动job

begin
  dbms_job.broken(294,TRUE);
end;

begin
  dbms_job.broken(294,FALSE);
end;

 

 

调整Job的运行时间

  BEGIN
    DBMS_JOB.next_date(495,next_date => to_date('20111029','yyyymmdd'));
  END;
  

 

调整Job

dbms_job.change(
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2,
instance  IN BINARY_INTEGER DEFAULT NULL,
force     IN BOOLEAN        DEFAULT FALSE);


例如：exec dbms_job.change(14144, NULL, NULL, 'SYSDATE + 3');