Go ORACLE Job Timing

another good article:http://www.cnblogs.com/java-pan/archive/2012/09/16/oracle_job.html

There are two ways to do a timed mission.

One is: Operating system timing, win timed tasks, Unix crontab
One is: database-level timing, she's more efficient,


There are a lot of table-level operations, it is recommended to use the database itself job queue, so convenient, high efficiency; If you use system-level timing,
Will add a lot of programming work, the cost is increased, but also prone to error, the more simple the probability of error is smaller.

Before using the job queue, we also have a simple configuration under which Oracle timed the job queue's background process is SNP, to start

SNP, first see if system mode supports

Sql> alter system enable restricted session;
Or
Sql> alter system disable restricted session;
Use the command above to change the session mode of the system to disenable restricted, creating conditions for SNP startup.


Then there is the boot parameter for the config job queue, and the SNP's startup parameters are in the Oracle initialization file.
JOB_QUEUE_PROCESSES=10 (oracle10gde default value)
Job_queue_interval=n

The first line defines the number of starts of the SNP process is 10, the normal female crown range is 0-36, depending on the number of tasks, can be configured
Different values.

The second line defines the system to wake the process once every few seconds. The default is 60, and the normal range is 1-3,600 seconds. In fact, the process finishes executing

After the current task, it goes to sleep, and after a period of sleep, the system's master is responsible for waking it up.
If the file does not have the above two lines, add it as described on the previous configuration. After the configuration is complete, you need to restart the database for it to take effect

。 Note: If the task requires a short interval of execution, the configuration of n is correspondingly smaller.


To view the details of the job queue, query the database dictionary User_jobs

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

Contains the following sub-procedures:

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

1.
The broken () process updates the status of a submitted work, typically used to mark a broken job as not broken.
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 this work will be marked as broken--true this work will be marked as broken, and Flase indicates that the work will be marked as not broken.
The Next_date parameter indicates when this work will run again. The default value for this parameter is the current date and time.

2.
The change () procedure is used to alter the settings of the specified work.
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)

This job parameter is an integer value that uniquely identifies the job.
What parameter is a PL/SQL code block that is run by this operation.
The Next_date parameter indicates when this work will be performed.
The interval parameter indicates the frequency of a work re-execution.

3.
The Interval () procedure is used to explicitly set the number of intervals between the re-execution of a work.
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 a work re-execution.

4.
The Isubmit () process is used to submit a job with a specific work 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 the job parameter is passed as an in parameter and includes a
The work number provided by the developer. If the supplied work number is already in use, an error is generated.


5.
The Next_date () procedure 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)

The job identifies a work that already exists. The Next_date parameter indicates the date and time that this work should be performed.

6.
Remove () procedure to delete a work that has been scheduled to run. This process takes 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 by the submit () procedure called for this work.
Work that is already running cannot be deleted by the calling program.

7.
The Run () procedure is used to perform a specified work immediately. This procedure receives only one parameter:

PROCEDURE Run (Job in Binary_ineger)

The job parameter identifies the work that will be performed immediately.

8.
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 () procedure. This value is used to uniquely identify a job.
What parameter is the PL/SQL code block that will be executed.
The Next_date parameter indicates when the job will run.
Interval parameter when this work will be re-executed.
The No_parse parameter indicates whether this work should be parsed at the time of submission or execution--true
Indicates that this PL/SQL code should be parsed the first time it executes,
False indicates that this PL/SQL code should be parsed immediately.

9.
The User_export () procedure returns a command that is used to schedule an existing work so that the work can be resubmitted.
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 an assigned job. The My_call parameter contains the required to resubmit this work in its current state
The text.

10.
What () process promises to reset this running command at work execution time. This process receives two parameters: job and what.

PROCEDURE what (Job in Binary_ineger,
What in Out varchar2)

The job parameter identifies a work that exists. What parameter indicates the new PL/SQL code that will be executed.


A simple example:

Create a test table
Sql> CREATE TABLE T (a date);

The table is created.

Create a custom procedure
sql> Create or replace procedure test as
2 begin
3 INSERT into a values (sysdate);
4 End;
5/

The process has been created.

Create Job
sql> variable JOB1 number;
Sql>
Sql> begin
2 Dbms_job.submit (: job1, ' Test; ', Sysdate, ' sysdate+1/1440 '); --1440 minutes per day, or one minute to run the test procedure once
3 END;
4/

The PL/SQL process has completed successfully.

Run Job
Sql> begin
2 Dbms_job.run (: JOB1);
3 END;
4/

The PL/SQL process has completed successfully.

Sql> Select To_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from A;

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

Delete Job
Sql> begin
2 Dbms_job.remove (: JOB1);
3 END;
4/

The PL/SQL process has completed successfully.

Here are the common views and parameters:

Data dictionary view about tasks in the task queue



View name Description
Dba_jobs tasks in this database that are defined in the task queue
Dba_jobs_running tasks that are currently running
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 if there is a lot of snapshot that require automatic refresh, increase it appropriately)

Job_queue_interval: Seconds (default is 60 seconds), depending on how often your job is scheduled, the job that runs once a day is set to the default value or a few minutes. (Do not set too small, so as not to affect performance)

Job_queue_keep_connection (System default Yes, I didn't find his role)

Dba_jobs and user_jobs. The field meaning of the dictionary view

field (column) type description
Unique identifier of the job number task
Log_user VARCHAR2 (30) the user who submitted the task
Priv_user VARCHAR2 (30) Users assigned to task permissions
Schema_user VARCHAR2 (30) User mode for syntactic analysis of tasks
Last_date date the last time the task was successfully run
Last_sec VARCHAR2 (8) hours, minutes and seconds of last_date date in HH24:MM:SS format
This_date date the start time of the task is running, or null if no task is running
This_sec VARCHAR2 (8) hours, minutes and seconds of this_date date in HH24:MM:SS format
Next_date date Time of the next scheduled run of the task
Next_sec VARCHAR2 (8) hours, minutes and seconds of next_date date in HH24:MM:SS format
Total_time number The total time, in seconds, required for the task to run
Broken VARCHAR2 (1) flag parameter, y indicates the task is interrupted and will not run later
INTERVAL VARCHAR2 (200) expression for calculating the next run time
Failures number task runs with no successive successes
What VARCHAR2 (2000) PL/SQL block for performing tasks
Current_session_label RAW MLSLABEL Trust Oracle Session break for this task
Clearance_hi RAW MLSLABEL Oracle maximum gap that the task can trust
Clearance_lo RAW MLSLABEL Oracle minimum gap that the task can trust
Nls_env VARCHAR2 (2000) NLS session settings for task runs
Misc_env RAW (32) Some other session parameters that the task runs


field meaning of view dba_jobs_running

Column data type description
SID number is currently running the session ID of the task
Unique identifier of the job number task
Failures number of consecutive unsuccessful executions
Last_date date of last successful execution
Last_sec VARCHAR2 (8) hours, minutes and seconds of last_date date in HH24:MM:SS format
This_date date the start of the task is currently running
This_sec VARCHAR2 (8) hours, minutes and seconds of this_date date in HH24:MM:SS format


Task repeat run interval and interval design

The interval of time that the algorithm task repeats runs depends on the date expression set in the interval parameter. Here is a detailed discussion of how to set the interval parameter to accurately meet our mission needs. Generally speaking, there are three timing requirements for a scheduled execution of a task.

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 6 shows some examples of this time interval setting.

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

Go ORACLE Job Timing