Oracle Database Jobs Task Queue Example

Jobs is one of the Oracle database objects, timed tasks. Add a task to the task queue by calling the stored procedure dbms_job.submit.

View the Dbms_job package, which has two stored procedures:

PROCEDURE isubmit (Job in Binary_integer,
What in VARCHAR2,
Next_date in Date,
Interval in VARCHAR2 DEFAULT ' null ',
No_parse in BOOLEAN DEFAULT FALSE);
--Submit a new job with a given job number.

PROCEDURE Submit (Job out Binary_integer,
                    What in VARCHAR2,
                    next_date in Date DEFAULT sysdate,
                    interval in VARCHAR2 DEFAULT ' null ',
                    no_parse in BOOLEAN DEFAULT FALSE,

--Bug 1346620:replace pkg VARs with constants.
--Default For instance = dbms_job.any_instance.
Instance in Binary_integer DEFAULT 0,
Force in BOOLEAN DEFAULT FALSE);
--Submit a new job. Chooses JOB from the sequence sys.jobseq.
--instance and force are added for JOBQ queue affinity
--If FORCE is TRUE, then any positive an integer is acceptable as the job
--instance. If FORCE is FALSE, then the specified instance must to be running;
--Otherwise the routine raises an exception.
--for example,
--variable x number;
--Execute Dbms_job.submit (: X, ' Pack.proc (' arg1 '); ', Sysdate, ' sysdate+1 ');

Note that we are using the procedure submit this stored procedure.

1. Basic grammar

Submit (Job out Binary_integer,
What in VARCHAR2,
Next_date in date DEFAULT sysdate,
Interval in VARCHAR2 DEFAULT ' null ',
No_parse in BOOLEAN DEFAULT FALSE,
);

Description
Job: Output variable, is the number of this task in the task queue
What: executed stored procedure or SQL statement
Next_date: The first time a task is executed
Interval: Note that not every time the interval, but the next execution time, before the execution of this expression evaluated the next execution time, so the general is sysdate plus how much calculated.

In general, we only need to change the first 4 parameters on it, the task next execution time is in the beginning of this task calculated.

2. Create Test table

CREATE TABLE Job_test (a date);

3. Create statements

--/
DECLARE
Jobtest number;
Begin
Dbms_job.submit (
Job => Jobtest,
What => ' insert into job_test values (sysdate); ',
Next_date => sysdate+0.01,
Interval => ' sysdate+1/24 '
);
End
/

If Next_date fills in Sysdate,jobs, it will be executed once.

Next_date can also use the To_data () function to format time so that the time is more accurate:
Next_date => to_date (' 20150623110000 ', ' YYYY:MM:DD HH24:MI:SS ') to set a starting time.

4. Removal of Scheduled Tasks
Remove to fill in the ID of jobs.

--/
Begin
Dbms_job.remove (101);
End
/

5. Mandatory execution of Scheduled tasks

--/
Begin
Dbms_job.run (101,true);
End
/

Table 1 Examples of some simple interval parameter settings

Describe interval parameter values
Run once a day ' sysdate + 1 '
Run once per hour ' sysdate + 1/24 '
Run 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

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 '
12 O'Clock ' TRUNC (Last_day (sysdate) + 1) ' At midnight on the first day of the month
Each quarter on the last day of the evening 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
Every Saturday and 6:10 A.M. ' TRUNC (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 encounter 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.