Oracle timer (job) Statement summary for each time period

Original Author: blog garden Yihui original text: Link

For DBAs, the database job is no longer familiar, because it is often necessary for the database to automatically execute some scripts at regular intervals, or to back up the database, or to refine the data, or to optimize the database performance, this includes re-indexing and so on. However, the processing of Oracle timer job time is ever-changing. Today I summarize the commonly used statements as follows:

Before summing up, describe the job parameters one by one:

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. 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.

1 declare
2 jobno number;
3 begin
4 dbms_job.submit (
5 jobno,
6 'P _ dosomething; ', -- what
7 to_date ('123', 'yyyy-mm-dd hh24: MI: ss'), -- next_date, which can be left blank
8 'interval time string' -- interval, key settings
9 );
10 commit;
11end;
12

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_months (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

Of course, the above list cannot take care of all aspects, but with a slight change, countless examples can be derived. If you are smart, you will certainly be able to. I will not be arrogant here.

For more information, see liunx.cnblogs.com)

Supplement:
Parameter Value of Interval
'Trunc (sysdate + 1) 'At midnight every day )'
Every morning 08:30 'trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'
Next _ day (trunc (sysdate), ''tuesday'') + 12/24 at every Tuesday'
'Trunc (last_day (sysdate) + 1) 'at midnight on the first day of each month )'
'Trunc (add_months (sysdate + 2/24, 3), 'q')-100' at on the last day of each quarter'
'Trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sunday ") + (6 × 60 + 10) /(24 × 60 )'

Select * From user_jobs
Query job records

1. Make sure that the Oracle working mode allows start of the task queue manager
Oracle regularly executes the "Job Queue" background program as a SNP process. To start the SNP process, first ensure that the whole system mode can start the SNP process, the following command must be executed as a DBA:
Svrmgrl>; Alter system enable restricted session;
Or SQL>; Alter system disenable restricted session;
Use the above command to change the system session mode to disenable restricted, which creates conditions for the initiation of SNP.

2. Make sure that the startup parameters of the task queue manager have been configured in the Oracle system.
The launch parameter of SNP is located in the Oracle initialization file, which is placed in the $ ORACLE_HOME/DBS path. If the Oracle SID is myora8, the initialization file is initmyora8.ora, the description of the SNP startup parameters in the file is as follows:
Job_queue_process = N
Job_queue_interval = N
The first line defines the number of initiation of the SNP process as N. The default value is 0, and the normal definition range is 0 ~ 36. You can configure different values based on the number of tasks.
The second line defines the system to wake up the process every n seconds. The default value is 60 seconds. The normal range is 1 ~ 3600 seconds. In fact, the process enters the sleep state after the current task is executed. 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.

3. Add the task to the task queue of the database.
Call the stored procedure in the dbms_job package of Oracle to add the task to the task queue:
Dbms_job.submit (job out binary_integer,
What in archar2,
Next_date in date,
Interval in varchar2,
No_parse in Boolean)
Where:
● Job: output variable, which is the number of the task in the task queue;
● What: the name of the task to be executed and its input parameters;
● Next_date: the time when the task is executed;
● Interval: The interval between tasks.
The interval parameter in dbms_job.submit is discussed in detail below. Strictly speaking, interval refers to the interval from the last execution to the next execution. When interval is set to null, the job is deleted from the queue after the execution is completed. If we need to periodically execute the job, we need to use 'sysdate + M.
Before adding a task to the task queue, determine the database user who executes the task. If the user is Scott, make sure that the user has the permission to run the dbms_job package. If not, grant permissions to Scott as a DBA:
Svrmgrl>; grant execute on dbms_job to Scott;

4. Write the tasks to be executed as stored procedures or PL/SQL program segments that can be executed by other databases
For example, we have created a stored procedure named my_job. Log On As Scott in SQL/plus and run the following command:
SQL>; Variable N number;
SQL>; begin
Dbms_job.submit (: n'my _ job; ', sysdate,
'Sysdate + 100 ');
Commit;
End;
/
The system prompts that the execution is successful.
SQL>; print: N;
The system prints the ID of the task. For example, the result is 300.
As shown above, we have created a task with the task number 300 executed every 4 minutes. You can use the data dictionary user_jobs provided by Oracle to view the execution of this task:
SQL>; select job, next_date, next_sec, failures, broken from user_jobs;
The execution result is as follows:
Job next_date next_sec failures broken
300 2000/10/10 11:45:15 0 n
This indicates that the task with the task number 300 will be executed at 11:45:15 next time, and the execution failure record of this task is 0 times. Note: When an error occurs during job execution, Oracle records it in the log. The number of failures is automatically increased by 1 each time. When the number of failed tasks reaches 16, Oracle marks the job as a broken. Since then, Oracle will not continue to execute it until dbms_job.broken is re-set to not broken during the User call process, or forcibly call dbms_job.run to re-execute it.
In addition to the submit stored procedure discussed above, Oracle also provides many other stored procedures to operate tasks. For example, dbms_job.change, dbms_job.what, and dbms_job.interval can be used to modify submitted tasks. To delete a task, run dbms_job.remove (N), where N is the task number.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/ylqmf/archive/2010/01/13/5185595.aspx