Oracle dbms_scheduler

 --建立job

begin

dbms_scheduler.create_job (

   job_name           =>  'delete_history_log',

   job_type           =>  'STORED_PROCEDURE',

   job_action         =>  'manage_logs_pkg.delete_history_logs',

   start_date         =>  to_date('20211208080000','yyyymmdd hh24miss'),

   repeat_interval    =>  'freq=daily; byhour=8', --Run at 8 am every day --'freq=minutely; interval=5' --每五分鐘

   enabled            =>  TRUE);

END;

--確認job建立成功

SELECT job_name, job_type, job_action,last_start_date, next_run_date 

FROM USER_SCHEDULER_JOBS where job_name=upper('delete_history_log');

--早上8:00後查看job啟動成功

select job_name,log_date,status,req_start_date,actual_start_date,run_duration,session_id

 from user_scheduler_job_run_details where job_name= upper('delete_history_log');

--刪除JOB

DBMS_SCHEDULER.drop_job (job_name=>'test_sched_job');

--enable / disable a job

DBMS_SCHEDULER.disable (name=>'test_sched_job');

DBMS_SCHEDULER.enable (name=>'test_sched_job');

--手動執行JOB

DBMS_SCHEDULER.run_job (job_name => 'test_sched_job', use_current_session => TRUE);

--設定attribute(例如NLS_LANGUAGE)

BEGIN

   dbms_scheduler.set_attribute('test_sched_job','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' ');

   dbms_scheduler.enable('test_sched_job');

END;

--調整JOB參數

Begin 
dbms_scheduler.disable( 'test_sched_job' );

DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'test_sched_job', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');--change value--as per need

dbms_scheduler.enable('test_sched_job');
End;

--若要作完整架構的排成請參考:

program > schedule > job

https://www.support.dbagenesis.com/post/scheduling-jobs-with-dbms_scheduler

--repeat_interval   範例  (https://www.twblogs.net/a/5d498143bd9eee541c306970)

1.每週5的時候運行，以下3條實現功能一樣

REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=FRI';   

REPEAT_INTERVAL => 'FREQ=WEEKLY; BYDAY=FRI';   

REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=FRI'; 

2.每隔一週運行一次，僅在周5運行

REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI’;  

3.每月最後一天運行

REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1'; 

4.在3月10日運行

REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10'; 

REPEAT_INTERVAL => 'FREQ=YEARLY; BYDATE=0310'; 

5.每10隔天運行

REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10'; 

6.每天的下午4、5、6點時運行

REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=16,17,18’;

7.每月29日運行

REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=29'; 

8.每年的最後一個周5運行

REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=-1FRI'; 

9.每隔50個小時運行

REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=50';