sys. dbms_ scheduler. create_ Job creates scheduled tasks (more powerful and rich functions)

Catalog

job The creation and deletion of

JOB Timing frequency in repeat_interval Detailed explanation

---

job The creation and deletion of

begin
  sys.dbms_scheduler.create_job(job_name            => 'GIS.TEST_JOB',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'pc_test',
                                start_date          => to_date('06-08-2019 9:10:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=MINUTELY;Interval=5',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => ' Test stored procedures ');
end;
/

explain ：

 
1、job_name：  The name of the task 
2、job_type： There are three types ,PL/SQL Block、Stored procedure、Executable
3、job_action： according to job_type Different , There are different meanings 
 If job_type What is specified is the stored procedure , You need to specify the name of the stored procedure ;
 If job_type Specifies the PL/SQL block , You need to enter the complete PL/SQL Code ;
 If job_type Specified external program , You need input script Or the instruction name of the operating system 
4、start_date： Starting time 
5、repeat_interval： Time interval between runs , The above example is every day 23 Click to run once 
6、end_date： Due time 
7、enabled： Automatically activate after creation 
8、auto_drop： Default true, When job Whether to delete directly after the execution expires job
9、comments： remarks 

Data cycle setting and example ：

1. Once a week 5 Run when , following 3 The function of the bar is the same 
REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=FRI';   
REPEAT_INTERVAL => 'FREQ=WEEKLY; BYDAY=FRI';   
REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=FRI'; 
 
2. Run every other week , Only in weeks 5 function 
REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI’;  
 
3. Run on the last day of each month 
REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1'; 
 
4. stay 3 month 10 Daily operation 
REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10’; 
REPEAT_INTERVAL => 'FREQ=YEARLY; BYDATE=0310'; 
 
5. Every time 10 Run the next day 
REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10’; 
 
6. Every afternoon 4、5、6 Run at point 
REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=16,17,18’;
 
7. monthly 29 Daily operation 
REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=29’; 
 
8. The last week of the year 5 function 
REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=-1FRI’; 
 
9. every other 50 Run for hours 
REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=50’;  

-- Delete job

begin

 dbms_scheduler.drop_job('xxxx');

end;

-- Ban 

begin

 dbms_scheduler.disable('xxxx');

end;

-- start-up 

begin

 dbms_scheduler.enable('xxxx');

end;

exec dbms_scheduler.enable('demo_job');  -- Enable jobs  
 
exec dbms_scheduler.disable('demo_job');  -- Ban jobs 

exec dbms_scheduler.run_job('demo_job');  -- perform jobs  
 
exec dbms_scheduler.stop_job('demo_job');  -- stop it jobs  

exec dbms_scheduler.drop_job('demo_job');  -- Delete jobs 



user_scheduler_jobs  View all job Information 

user_scheduler_running_jobs  Look at all the running job

user_scheduler_job_run_details job Run log 

User_Scheduler_Job_Log job job journal 

JOB Timing frequency in repeat_interval Detailed explanation

REPEAT_INTERVAL The structure is ：REPEAT_INTERVAL => 'Freq=Minutely;Interval=5'

FREQ Keyword is used to specify the time period of the interval , The optional parameters are ：YEARLY（ year ）, MONTHLY（ month ）, WEEKLY（ Zhou ）, DAILY（ Japan ）, HOURLY（ when ）, MINUTELY（ branch ）, SECONDLY（ second ） Equal unit .

INTERVAL Keyword is used to specify the frequency of the interval ,

The range of values that can be specified is from 1-999.

BYHOUR  Specify the hours of the day .

The range of values that can be specified is from 1-24.16,17,18 It means... Every afternoon 4、5、6 spot .

BYDAY Keyword is used to specify which day of the week to run .

“MON” | “TUE” | “WED” | “THU” | “FRI” | “SAT” | “SUN”

BYMONTHDAY Keyword is used to specify which day of the month .

1,2,3... Any day ,-1 Indicates the last day of each month .

BYMONTH  Keyword is used to specify the month of each year .

“JAN” | “FEB” | “MAR” | “APR” | “MAY” | “JUN” | “JUL” | “AUG” | “SEP” | “OCT” | “NOV” | “DEC”

BYDATE Specify Date .

0310 It means 3 month 10 Japan .

for example ：

Run every Friday .（ All three examples are equivalent .）

1. FREQ=DAILY; BYDAY=FRI;  
2. FREQ=WEEKLY; BYDAY=FRI;  
3. FREQ=YEARLY; BYDAY=FRI;  

Set the task to run every other week , And only in weeks 5 function ：

1. FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;  

Run on the last day of each month

1. FREQ=MONTHLY; BYMONTHDAY=-1;  

It opens on March 10 .（ The two examples are equivalent ）

1. FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;  
2. FREQ=YEARLY; BYDATE=0310;  

Set tasks every 10 Run the next day ：

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

Set tasks every afternoon 4、5、6 Run at point ：

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

Set tasks every month 29 Daily operation ：

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

Set tasks in the last week of the year 5 function ：

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

Set tasks every 50 Run for hours ：

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

repeat_interval => 'FREQ=HOURLY; INTERVAL=2'
every other 2 Run once an hour job

repeat_interval => 'FREQ=DAILY'
Run once a day job

repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,WED,FRI"
Weekly 1,3,5 function job

repeat_interval => 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30'
From year to year 3,6,9,12 Of the month 30 No. operation job

Now that we're here repeat_interval, You may want to ask :" Is there a simple way to get , Or assess job Each run time of , And the next running time ?"dbms_scheduler Package provides a process evaluate_calendar_string, You can easily complete this requirement !