Summary of Oracle Database timer Job writing at various times

InOracle DatabaseWe often useTimer JobTo automatically execute scripts, back up databases, refine data, or optimize database performance, including re-indexing. However, the processing of Oracle timer Job time is ever-changing and flexible. This article summarizes the writing of some Oracle Database timer jobs in various time periods. Next we will introduce these.

Before summing up, let's take a look at the Job parameters:

The Job parameter is the binary_integer returned by the Submit () process.

What parameter is the PL/SQL code block to be executed.

The next_date parameter indicates when the job will be run. You can leave this value unspecified when writing a Job.

The interval parameter indicates when the job will be re-executed.

The Interval value is the key to determining when a Job is re-executed.

For example, a stored procedure p_dosomethings needs to be executed at different intervals.

 
 

  
  
Declare
  
  
 
  
  
Jobno number;
  
  
 
  
  
Begin
  
  
 
  
  
Dbms_job.submit (
  
  
 
  
  
Jobno,
  
  
 
  
  
'P _ dosomething ', -- what
  
  
 
  
  
To_date (), -- next_date, optional
  
  
 
  
  
'Interval time string' -- Interval, key settings
  
  
 
  
  
);
  
  
 
  
  
Commit;
  
  
 
  
  
End;
 
 

1. Execution per minute

Interval => TRUNC (sysdate, 'mi') + 1/(24*60)

2. daily scheduled execution

Example: Execute at every day

Interval => TRUNC (sysdate) + 1 + 2/(24)

3. weekly scheduled execution

Example: Execute at every Monday

Interval => TRUNC (next_day (sysdate, 2) + 2/24 -- Monday, the second day of a week

4. scheduled monthly execution

For example, the task is executed at on the first day of every month.

Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 2/24

5. Periodical execution on a quarterly basis

For example, the statement is executed at on the first day of each quarter.

Interval => TRUNC (ADD_MONTH (SYSDATE), 3), 'q') + 2/24

6. scheduled execution every six months

For example, at a.m. on January 1, July 1 and January 1, January 1

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 2/24

7. Regular annual execution

For example, it is executed at on January 1, January 1 every year.

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 2/24

This article describes how to write an Oracle timer Job in various time periods. If you want to know