Oracle資料庫10g schedule job的常用操作:

Oracle資料庫10g schedule job的常用操作:

-- job 許可權

grant create job to somebody;

 

-- job 建立

begin

dbms_scheduler.create_job (

job_name => 'AGENT_LIQUIDATION_JOB',

job_type => 'STORED_PROCEDURE',

job_action => 'AGENT_LIQUIDATION.LIQUIDATION', --儲存過程名

start_date => sysdate,

repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0', -- 按月，間隔為1個(月),每月1號,凌晨1點

comments => '執行代理商清分程式'

);

end;

/

 

-- job 執行時間測試

DECLARE

start_date date;

return_date_after date;

next_run_date date;

BEGIN

start_date := sysdate;--to_timestamp_tz('10-OCT-2004 10:00:00','DD-MM-YYYY HH24:MI:SS');

return_date_after := start_date;

FOR i IN 1..10 LOOP

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',start_date, return_date_after, next_run_date);

DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date,'yyyy-mm-dd HH24:MI:SS'));

return_date_after := next_run_date;

END LOOP;

END;

/

 

-- job 查詢

select owner, job_name, state from dba_scheduler_jobs;

select job_name, state from user_scheduler_jobs;

 

-- job 啟用

begin

dbms_scheduler.enable('BACKUP_JOB');

end;

/

 

-- job 執行

begin

dbms_scheduler.run_job('COLA_JOB',TRUE); -- true代表同步執行

end;

/

 

-- job 停止(不太好用)

begin

dbms_scheduler.stop_job(job_name => 'COLA_JOB',force => TRUE);

end;

/

 

-- job 刪除(對停job來說好用)

begin

dbms_scheduler.drop_job(job_name => 'COLA_JOB',force => TRUE);)

end;

/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/701141/viewspace-343252/，如需轉載，請註明出處，否則將追究法律責任。