ORACLE JOB Settings

Job The parameters:

One: time interval execution (every minute, every day, weekly,: Monthly, Quarterly, half-yearly, yearly)

interval refers to the interval between the last execution end and the next start, and when the interval is set to NULL, the job execution ends,

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

1: Per minute execution

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

2: Regular daily execution

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

Example: Monthly 1st 1 o'clock in the morning

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

5: Quarterly Regular execution

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

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

6: Every six months regular execution

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

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

7: Regular annual execution

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

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

Second: After a specific interval, run the task repeatedly. Run the task at a specific date and time. Once the task completes successfully, the next execution should be after a specific time interval.

The first scheduling task needs the date algorithm is simpler, that is, ' 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 value
Run Once a day	' Sysdate + 1 '
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 run the task and delete it	Null

The task interval expression shown in table 1 does not guarantee that the next running time of a task is at a specific date or time, only the time interval between two runs of a task can be specified. 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 following day. However, if a user is hand-dbms_job at 4 o'clock in the afternoon. Run), the task is scheduled to be reset 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 quickly as possible, meaning that the execution begins as soon as the database is opened or the task queue is not busy, but at this point, the runtime has drifted from the original commit time to the later real run time. The constant drift of the next run time is a typical feature of a simple time interval expression.

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

Table 2. Example of a task timed to a specific date or time

td> every night 12 o'clock

description	interval parameter value
' 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) '
every quarter last One night 11 o'clock	' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
every Saturday and day 6:10 A.M.	' TRUNC least (Next_day (sysdate, ' SATURDAY '), Next_day (Sysdate, "SUNDAY"))) + (6X60+10)/(24x60) '

The third scheduling task requirement can not satisfy the requirement regardless of how the interval date expression is set. This is because the next run of 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 meet this situation. Of course, there must be some, we can through the task queue writing process to achieve. Here I just briefly introduce the following, to get the system time for the task to complete during the previous task queue execution, and then add the time interval to control the next task to be performed. Here is a prerequisite, that the current task itself must strictly adhere to their own time plan.

Before you add a task to the task queue, determine the database user who performed the task, and if the user is Scott, you need to ensure that the user has permission to execute the package dbms_job, and if not, grant the right to the Scott user as DBA:

Svrmgrl> Grant execute on Dbms_job to Scott;

4. Write the task you want to perform 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 the 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 for a successful execution.

Sql> print:n;

The system prints the number of this task, for example, the result is 300.

As above, we created a task with a task number of 300 performed every 4 minutes. The implementation of this task can be viewed user_jobs the data dictionary provided by Oracle:

Sql> select Job,next_date,next_sec,failures,broken from User_jobs;

The results of the implementation 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, the next time it will be performed in 2000/10/10 11:45:15, and the execution of this task is recorded 0 times. Note: When an error occurs in the execution job, Oracle records 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 will mark the job as broken. Thereafter, Oracle does not continue to execute it until the user invokes the procedure Dbms_job.broken, reset to not broken, or force the call to Dbms_job.run to execute it again.

In addition to the submit stored procedures we discussed above, Oracle also provides many other stored procedures to operate the task. 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, Every minute execution

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

2. Regular daily 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

Example: Monthly 1st 2 o'clock in the morning

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

5. Regular quarterly execution

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

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

6, every six months scheduled implementation

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

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

7, the annual regular implementation

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

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

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

The next_date parameter indicates when this work will be run. You can not specify this value when writing a job;

interval parameter when this work will be executed again.