Time Setting reference for Oracle Timer task (dbms_job)

The last one can be consulted as follows:
Every night 12 o'clock ' TRUNC (sysdate + 1) '
Daily 8:30 A.M. ' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '
Every Tuesday noon 12 o'clock ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '
Midnight of the first day of the Month 12 o'clock ' TRUNC (Last_day (sysdate) + 1) '
The last day of each quarter 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
Every Saturday and Sunday 6:10 A.M. ' TRUNC (LEAST (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6*60+10)/(24*60) '

Where Li_jobno is its ID, you can stop this task by this ID, the last to say is not to execute multiple times, you can manage it inside, found that has been running do not submit

Select TRUNC (sysdate, ' MONTH '), Last_day (sysdate) from dual

--the first and last day of each month

Run ' sysdate + 1 ' once a day
Run once per hour ' sysdate + 1/24 '
Run once every 10 minutes ' sysdate + 10/(60*24) '
Run once every 30 seconds ' sysdate + 30/(60*24*60) '
Run once every other week ' sysdate + 7 '
No longer runs the task and deletes it NULL
January 1 0 O'Clock Trunc (last_day (To_date (Extract (year fromsysdate) | | 12 ' | | ' (' Yyyy-mm-dd ')) +1

-------------------

Job Parameters:

One: time interval execution (per minute, daily, weekly,: Monthly, quarterly, semi-annually, per year)

Interval is the interval between the last execution and the next execution, and when interval is set to NULL, the job execution ends.

is removed from the queue. If we need the job to be executed periodically, use ' sysdate+m '.

1: Execute Every Minute

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

2: Regular execution every day

Example: Daily 1 o'clock in the morning execution

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

3: Regular Weekly execution

For example: Every Monday 1 o'clock in the morning execution

Interval=> TRUNC (Next_day (sysdate, ' Monday ')) +1/24

4: Regular Monthly execution

For example: 1st 1 o'clock in the morning every month to execute

Interval=>trunc (Last_day (sysdate)) +1+1/24

5: Quarterly Scheduled execution

For example, the first day of each quarter is 1 o'clock in the morning execution

Interval=> TRUNC (Add_months (sysdate,3), ' Q ') + 1/24

6: Every half-yearly scheduled execution

For example: Every July 1 and January 1 1 o'clock in the morning

Interval=> add_months (trunc (sysdate, ' yyyy '), 6) +1/24

7: Scheduled execution every year

For example: January 1 1 o'clock in the morning every year to execute

Interval=>add_months (Trunc (sysdate, ' yyyy '), 12) +1/24

Two:

Run the task repeatedly after a specific interval of time.
Run the task at a specific date and time.
After the task completes successfully, the next execution should be after a specific interval.
The first scheduling task requires a date algorithm that is simpler, i.e. ' sysdate+n ', where n is a time interval in days. Table 1 shows some examples of this time interval setting.

Table 1 Examples of some simple interval parameter settings

Describe interval parameter values
Run ' sysdate + 1 ' once a day
Run once per hour ' sysdate + 1/24 '
Run once every 10 minutes ' sysdate + 10/(60*24) '
Run once every 30 seconds ' sysdate + 30/(60*24*60) '
Run once every other week ' sysdate + 7 '
No longer runs the task and deletes it NULL

The task interval expression shown in table 1 does not guarantee that the next run time for a task will be at a specific date or time, only to specify the time interval between two runs of a task. For example, if a task is run for the first time at 12 o'clock in the morning and interval is specified as ' sysdate+1 ', the task is scheduled to be executed at 12 o'clock in the morning the next day. However, if a user is manually at 4 o'clock in the afternoon (dbms_job. Run), the task will be re-timed to 4 o'clock in the afternoon the next day. Another possible reason is that if the database is shut down or the task queue is so busy that the task cannot be executed on time at the scheduled point. In this case, the task will attempt to run as soon as possible, that is, as soon as the database is open or the task queue is not busy, but at this point the run time has shifted from the original commit time to the actual elapsed time. The constant "drift" of the next run time is a typical feature of simple time-interval expressions.

The second scheduling task requires a more complex time interval (interval) expression relative to the first one, and table 7 is an example of a interval setting that requires a task to be run at a specific time.

Table 2. Task example timed to a specific date or time

Describe interval parameter values
Every night 12 o'clock ' TRUNC (sysdate + 1) '
Daily 8:30 A.M. ' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '
Every Tuesday noon 12 o'clock ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '
Midnight of the first day of the Month 12 o'clock ' TRUNC (Last_day (sysdate) + 1) '
The last day of each quarter 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
Every Saturday and Sunday 6:10 A.M. ' TRUNC (LEAST (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60) '

The third scheduling task needs no matter how the interval date expression is set, it does not meet the requirements. This is because the next run time for a task is calculated at the beginning of the task, and at this point it is not known when the task ends. What happens when you encounter this situation? Of course there are ways to do this, and we can do that by writing the process for the task queue. Here I am just a brief introduction to the following, can be in the previous task queue execution process, get the task completed the system time, and then add the specified time interval, take this time to control the next task to be performed. There is a precondition that the task that is currently running must strictly follow its own time plan.

Before adding a task to the task queue, to determine the database user performing the task, and if the user is Scott, you need to make sure that the user has permission to execute the package dbms_job and, if not, to grant the rights to the Scott user as a DBA:
svrmgrl> Grant execute on Dbms_job to Scott;
4. The task to be performed is written as a stored procedure or other database executable PL/SQL program segment
For example, we have established a stored procedure with the name My_job, logged in as a Scott user in Sql/plus, and executed the following command:
sql> variable n number;
Sql> begin
Dbms_job.submit (: n ' my_job; ', sysdate,
' sysdate+1/360 ');
Commit
End; The
/
system prompts the execution to succeed.
Sql> print:n; The
System prints the number of this task, for example, the result is 300.
As above, we have created a task with a task number of 300 that executes every 4 minutes. You can view the execution of the task by user_jobs the data dictionary provided by Oracle:
sql> Select Job,next_date,next_sec,failures,brokenfrom user_jobs;
The results of the execution are as follows:
Job next_date next_sec failures Broken
2000/10/10 11:45:15 0 N
This represents a task with a task number of 300, and the next time the 2000/10/ 1011:45:15 execution, the execution of this task is logged 0 times. Note: When an error occurs in the execution job, Oracle logs it in the log, and the number of failures is automatically added to 1 each time. When the number of execution failures reaches 16, Oracle flags the job as broken. After that, Oracle no longer continues to execute it until the user invokes the procedure Dbms_job.broken, sets it back to Notbroken, or forces a call to Dbms_job.run to re-execute it.

In addition to the submit stored procedures we discussed above, Oracle provides many other stored procedures to manipulate tasks. For example: Dbms_job.change, Dbms_job.what, Dbms_job.interval can be used to modify the submitted task. To delete the task, simply run Dbms_job.remove (n), where n is the task number.

Summarize:

1. Execute Every Minute

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

2, daily scheduled execution

Example: Daily 2 o'clock in the morning execution

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

3. Regular weekly execution

For example: Every Monday 2 o'clock in the morning execution

Interval =>trunc (Next_day (sysdate, ' Monday ')) +2/24

4. Regular monthly execution

For example: 1st 2 o'clock in the morning every month to execute

Interval =>trunc (Last_day (sysdate)) +1+2/24

5, quarterly scheduled execution

For example, the first day of each quarter is 2 o'clock in the morning execution

Interval = TRUNC (Add_months (sysdate,3), ' Q ') +2/24

6, every half-yearly scheduled execution

For example: Every July 1 and January 1 2 o'clock in the morning

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

7. Scheduled execution every year

For example: January 1 2 o'clock in the morning every year to execute

Interval=>add_months (Trunc (sysdate, ' yyyy '), 6) +2/24

What parameter is the PL/SQL code block that will be executed;

The Next_date parameter indicates when the job will run. You can not specify this value when writing a job;

Interval parameter when this work will be re-executed.

Time Setting reference for Oracle Timer task (dbms_job)