Oracle scheduled task (dbms_job)

Today, we will summarize the Oracle Job Queue Manager (Job Queue), which will be convenient for future queries.
There are two ways to do a scheduled task:

One is: OS timing, win scheduled task, and Unix crontab.
One is: database-level timing, her efficiency is higher,

When there are a large number of table-level operations, we recommend that you use the Job Queue of the database itself, which is convenient and efficient. If you use system-level timing,
It will increase a lot of programming work, increase the cost, and make it easy to make mistakes. The simpler the case, the less likely the error will be.

Before using job queue, we also need to simply configure that the background process for Oracle to regularly execute job queue is SNP and should be started

SNP: First, check whether the system mode supports

SQL> alter system enable restricted session;
Or
SQL> alter system disenable restricted session;

Use the preceding command to change the system session mode to disenable restricted, and create conditions for the initiation of SNP.

Then, configure the startup parameter of Job Queue. the startup parameter of SNP is located in the Oracle initialization file,
Job_queue_processes = 10 (oracle10gde default value)
Job_queue_interval = N

The first line defines that the number of started SNP processes is 10, and the normal range of female crown is 0-36. You can configure the number of started SNP processes according to the number of tasks.
Different values.

The second line defines the system to wake up the process every few seconds. The default value is 60, and the normal range is 1-seconds. In fact, the process is completed

After the current task, it enters the sleep state. After a period of sleep, the general control of the system will wake it up.
If the file does not contain the above two lines, add them according to the above configuration. After the configuration is complete, you need to restart the database to make it take effect.

. Note: If the task requires a short execution interval, the configuration of N should be smaller accordingly.

View Job Queue details and query database dictionary user_jobs

Eg:
SQL> select job, next_date, next_sec, broken from user_jobs;

It contains the following sub-processes:

Broken () process.
Change () process.
Interval () process.
Isubmit () process.
Next_date () process.
Remove () process.
Run () process.
Submit () process.
User_export () process.
What () process.

1,
The broken () process updates the status of a submitted job. It is typically used to mark a broken job as a non-broken job.
This process has three parameters: Job, broken, and next_date.

Procedure broken (job in binary_integer,
Broken in Boolean,
Next_date in date: = sysdate)

The job parameter is the work number, which uniquely identifies the work in the problem.
The broken parameter indicates whether the job is marked as broken -- true indicates that the job is marked as broken, and flase indicates that the job is marked as not broken.
The next_date parameter indicates when the job will run again. The default value of this parameter is the current date and time.

2,
The change () process is used to change the settings of a specified job.
This process has four parameters: job, what, next_date, and interval.

Procedure change (job in binary_integer,
What in varchar2,
Next_date in date,
Interval in varchar2)

The job parameter is an integer that uniquely identifies the job.
What parameter is a PL/SQL statementCodeBlock.
The next_date parameter indicates when the job will be executed.
The interval parameter indicates the frequency of re-execution.

3,
The interval () process is used to explicitly set the time interval between re-execution of a job.
This process has two parameters: job and interval.

Procedure interval (job in binary_integer,
Interval in varchar2)

The job parameter identifies a specific job. The interval parameter indicates the frequency of re-execution.

4,
The isubmit () process is used to submit a job with a specific job number.
This process has five parameters: job, what, next_date, interval, and no_parse.

Procedure isubmit (job in binary_ineger,
What in varchar2,
Next_date in date,
Interval in varchar2,
No_parse in booean: = false)

The only difference between this process and the submit () process is that this job parameter is passed as an in parameter and includes
The employee ID provided by the developer. If the provided work number is used, an error is generated.

5,
The next_date () process is used to explicitly set the execution time of a job. This process receives two parameters: job and next_date.

Procedure next_date (job in binary_ineger,
Next_date in date)

A job identifies an existing job. The next_date parameter indicates the date and time when the job should be executed.

6,
Remove () to delete a scheduled job. This process receives a parameter:

Procedure remove (job in binary_ineger );

The job parameter uniquely identifies a job. The value of this parameter is the value of the job parameter returned when submit () is called for this job.
A running job cannot be calledProgramDelete.

7,
The run () process is used to immediately execute a specified job. This process only receives one parameter:

Procedure run (job in binary_ineger)

The job that will be executed immediately.

8,
When using the submit () process, the work is properly planned.
This process has five parameters: job, what, next_date, interval, and no_parse.

Procedure submit (job out binary_ineger,
What in varchar2,
Next_date in date,
Interval in varchar2,
No_parse in booean: = false)

The job parameter is the binary_ineger returned by the submit () process. This value uniquely identifies a job.
What parameter is the PL/SQL code block to be executed.
The next_date parameter specifies when the job will be run.
The interval parameter indicates when the job will be re-executed.
The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- true
Indicates that this PL/SQL code should perform syntax analysis when it is executed for the first time,
False indicates that the PL/SQL code should be analyzed immediately.

9,
The user_export () process returns a command to schedule an existing job so that the job can be submitted again.
This program has two parameters: job and my_call.

Procedure user_export (job in binary_ineger,
My_call in out varchar2)

The job parameter identifies a scheduled job. The my_call parameter is included in its current state. It is required to resubmit the job.
.

10,
The what () process promises to reset the running command during job execution. This process receives two parameters: job and what.

Procedure what (job in binary_ineger,
What in out varchar2)

The job parameter identifies an existing job. What parameter indicates the new PL/SQL code to be executed.

A simple example:

Create test table
SQL> Create Table A (a date );

The table has been created.

Create a custom Process
SQL> Create or replace procedure test
2 begin
3 insert into a values (sysdate );
4 end;
5/

The process has been created.

Create a job
SQL> variable job1 number;
SQL>
SQL> begin
2 dbms_job.submit (: job1, 'test; ', sysdate, 'sysdate + 100'); -- run the test process once every minute for 1/1440 minutes.
3 end;
4/

The PL/SQL process is successfully completed.

Run job
SQL> begin
2 dbms_job.run (: job1 );
3 end;
4/

The PL/SQL process is successfully completed.

SQL> select to_char (A, 'yyyy/MM/DD hh24: MI: ss') time from;

Time
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24

Delete a job
SQL> begin
2 dbms_job.remove (: job1 );
3 end;
4/

The PL/SQL process is successfully completed.

The following describes common views and parameters:

Data Dictionary view of tasks in task queue

View name	Description
Dba_jobs	Tasks defined in the task queue in this database
Dba_jobs_running	Currently running task
User_jobs	Tasks owned by the current user

Job_queue_processes> = 1 (if the system runs a lot of jobs at the same time, or there are a lot of snapshots that require automatic refresh, increase them as appropriate)

Job_queue_interval: the number of seconds (60 seconds by default). It depends on the scheduling frequency of your job. For a job that runs once a day, it can be set to the default value or several minutes. (Do not set too small to avoid affecting performance)

Job_queue_keep_connection)

Dba_jobs and user_jobs. Field meanings in the dictionary View

Field (column)	Type	Description
Job	Number	Unique ID of a task
Log_user	Varchar2 (30)	Submitter
Priv_user	Varchar2 (30)	Users with task Permissions
Schema_user	Varchar2 (30)	User Mode for task syntax analysis
Last_date	Date	Time when the last task was successfully run
Last_sec	Varchar2 (8)	For example, hh24: mm: the hour, minute, and second of the SS format last_date.
This_date	Date	The start time of the running task. if the task is not running, it is null.
This_sec	Varchar2 (8)	For example, hh24: mm: this_date in SS format: hour, minute, and second
Next_date	Date	Time of the next scheduled task
Next_sec	Varchar2 (8)	For example, hh24: mm: next_date in SS format: hour, minute, and second
Total_time	Number	The total time required for running the task, in seconds.
Broken	Varchar2 (1)	Mark parameter. y indicates that the task is interrupted and will not be run later.
Interval	Varchar2 (200)	Expression used to calculate the next line time
Failures	Number	Number of consecutive unsuccessful tasks
What	Varchar2 (2000)	PL/SQL block for task execution
Current_session_label	Raw mlslabel	Trust of this taskOracle Session character
Clearance_hi	Raw mlslabel	Trusted tasksOracle Maximum gap
Clearance_lo	Raw mlslabel	Trusted tasksOracle Minimum clearance
Nls_env	Varchar2 (2000)	NLS session settings for task running
Misc_env	Raw (32)	Other session parameters for task running

View dba_jobs_running field meaning

Column	Data Type	Description
Sid	Number	Session ID of the currently running task
Job	Number	Unique identifier of a task
Failures	Number	Number of consecutive unsuccessful executions
Last_date	Date	Date of the last successful execution
Last_sec	Varchar2 (8)	For example, hh24: mm: the hour, minute, and second of the SS format last_date.
This_date	Date	Start date of the currently running task
This_sec	Varchar2 (8)	For example, hh24: mm: this_date in SS format: hour, minute, and second

Task repetition interval and interval Design

AlgorithmThe interval between repeated tasks depends on the date expression set in the interval parameter. Next we will discuss in detail how to set the interval parameter to meet our task requirements accurately. Generally, there are three requirements for scheduled execution of a task.

After a specific interval, the task is run repeatedly.

Run the task on a specific date and time.

After the task is completed successfully, the next execution should be after a specific interval.

The date algorithm required by the First Scheduling task is relatively simple, that is, 'sysdate + N'. Here N is a time interval in days. Table 6 provides examples of such Interval Settings.

Table 6 Example of simple interval parameter settings

description	interval parameter value
run once a day	'sysdate + 1'
run once every hour	'sysdate + 123'
Run Once every 10 minutes	'sysdate + 10/(60*24) '
run once every 30 seconds	'sysdate + 30/(60*24*60) '
run once every week	'sysdate + 7'
stop running the task and delete it	null

The task interval expression shown in Table 6 does not guarantee that the next running time of a task is at a specific date or time. It can only specify the interval between two running tasks. For example, if a task runs for the first time at a.m. and interval is set to 'sysdate + 1', the task is scheduled to be executed at a.m. on the next day. However, if a user executes the task manually at four o'clock P.M. (dbms_job.run), the task will be rescheduled to four o'clock P.M. the next day. Another possible reason is that if the database is closed or the task queue is so busy that the task cannot be executed on time at the scheduled time point. In this case, the task will try to run as soon as possible, that is, as long as the database is opened or the task queue is not busy, the execution starts, the running time has shifted from the original commit time to the actual running time later. This continuous "drift" of the next running time is a typical feature using a simple time interval expression.

The second type of scheduling task requires a more complex interval expression than the first type. Table 7 is an example of how to set the interval for running the task at a specific time.

Table 7. Examples of tasks scheduled to a specific date or time

Description	Interval parameter value
Every day at midnight	'Trunc (sysdate + 1 )'
08:30 every morning	'Trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'
Every Tuesday	'Next _ day (trunc (sysdate), ''tuesday'') + 100'
Midnight on the first day of each month	'Trunc (last_day (sysdate) + 1 )'
PM on the last day of each quarter	'Trunc (add_months (sysdate + 2/24, 3), 'q')-100'
Every Saturday and 06:10 AM	'Trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sun Day ") + (6 × 60 + 10)/(24 × 60)'

no matter how you set the interval date expression, the third type of scheduling task cannot meet the requirements. In this case, the next running time of a task is calculated at the start of the task, but the task does not know when it will end. What should I do in this situation? Of course there must be some solutions. We can achieve this by writing the process for the task queue. Here I will just briefly introduce the following: You can get the system time of task completion during the execution of the previous task queue, and then add the specified time interval, take this time to control the next task to be executed. There is a precondition that the current running task must strictly abide by its own schedule.