Oracle timed Task (2)-dbms_job__oracle

Before Oracle 10g, you can use Dbms_job to manage timed tasks.
After 10g, Oracle introduced Dbms_scheduler to replace the previous dbms_job,
In terms of functionality, it provides more powerful functionality and more flexible mechanism/management than dbms_job.

Dbms_job Bag
The Dbms_job package contains a number of processes and methods

Name	Type	Describe
Dbms_job. Isubmit	Process	Submit a new task, the user specifies a task number Dbms_job.isubmit ( Job in Binary_integer, What in VARCHAR2, Next_date in Date, Interval in VARCHAR2 DEFAULT ' NULL ', No_parse in BOOLEAN DEFAULT FALSE); *no_parse indicates whether to parse job Pl/sql in time of submission (FALSE) or execution (TRUE) EXEC dbms_job.isubmit (+, ' begin myproc;end; ', sysdate);
Dbms_job. SUBMIT	Process	Submit a new task, the system specifies a task number Dbms_job.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, INSTANCE in Binary_integer DEFAULT 0, FORCE in BOOLEAN DEFAULT FALSE);
Dbms_job. REMOVE	Process	Remove an existing task from the queue Dbms_job.removejob in Binary_integer);
Dbms_job. Change	Process	change user-defined task parameters Dbms_job.change ( Job in Binary_integer, What in VARCHAR2, Next_date in Date, Interval in VARCHAR2, Instance in Binary_integer DEFAULT NULL, Force in BOOLEAN DEFAULT FALSE); exec dbms_job.change (100,null,null, ' sysdate+3 ');
Dbms_job. WHAT	Process	Change Pl/sql Task definition Dbms_job.what ( Job in Binary_integer, what in VARCHAR2);
Dbms_job. Next_date	Process	Change the next time a task runs Dbms_job.next_date ( Job in Binary_integer, Next_date in date);
Dbms_job. INTERVAL	Process	Change the interval at which a task runs Dbms_job.interval ( Job in Binary_integer, Interval in VARCHAR2); exec dbms_job.interval (M, ' TRUNC (sysdate) +1 ');
Dbms_job. Broken	Process	You can also restart a task by suspending it, not allowing it to run repeatedly. Dbms_job.broken ( Job in Binary_integer, Broken in BOOLEAN, Next_date in date DEFAULT sysdate); EXEC Dbms_job.broken (100,false,next_day (sysdate, ' MONDAY '));
Dbms_job. RUN	Process	Executes the task immediately in the current session. If the task is broken state, instead of broken. Dbms_job.run ( Job in Binary_integer, Force in BOOLEAN DEFAULT FALSE);
Dbms_job. INSTANCE	Process	For the RAC Environment, assign an instance to the execution of the job. Dbms_job.instance ( Job in Binary_integer, Instance in Binary_integer, Force in BOOLEAN DEFAULT FALSE); Select Instance_number from Gv$instance; EXEC dbms_job.instance (100, 1);
Dbms_job. User_export	Process	Creates a literal string that is used to recreate a task Dbms_job.user_export ( Job in Binary_integer, Mycall in Out VARCHAR2); Declare L_sqltxt VARCHAR2 (1000); Begin Dbms_job.user_export (100,l_sqltxt); Dbms_output.put_line (L_sqltxt); End

Note: Make sure to commit immediately after you perform any of the above creation/deletion/change tasks.
You must issue a commits statement immediately after the statement.
Otherwise, not only will the change be invalid, it may even cause some strange phenomena, such as job stop, remember.

Parameter description
All procedures in the Dbms_job package have an identical set of common parameters.
1) Job
A parameter job is an integer that uniquely identifies a task.
This parameter can be assigned either by the user or by the system automatically.
Dbms_job. The submit process automatically assigns a task number by obtaining the next value of the sequence sys.jobseq,
The task number is returned as an out parameter, so the caller can then identify the committed task.
Dbms_job. The Isubmit procedure specifies an identification number for the task by the caller.
If a duplicate task number is specified, the Isubmit process throws an error.
When the database is exported or imported, the task number is also preserved.
The occurrence of a task number conflict is most likely to occur when performing an import/export operation that contains data for a task.

2) what
The What parameter is a string that can be converted to a legitimate pl/sql call, which is automatically executed by the task queue.
The length of the What parameter is 4,000 bytes.
The value of this parameter is typically a call to a pl/sql stored procedure. Pl/sql calls must be separated by semicolons.
Another practical experience is that it is best to encapsulate stored procedure calls in an anonymous block to avoid some of the more inexplicable errors.
For example, in general, the What parameter can be referenced in this way: what => ' my_proc;
However, the more secure reference should be written as follows: What => ' begin my_proc; End; '
At any time, we can change the task definition only by changing the What parameter.
However, it should be noted that the user's current session settings are also recorded and become part of the task's operating environment by changing the What parameter to change the task definition.
If the current session settings differ from the session settings when the task was originally submitted, it is possible to change the task's running behavior.

3) Next_date
The Next_date parameter is the time to schedule the next run of the task in the task queue.
This parameter is for Dbms_job. Submit and Dbms_job. Broken these two processes are indeed the current time of the system, meaning that the task will run immediately.
When you assign the Next_date parameter of a task to NULL, the next time the task runs will be specified as January 1, 4000,
This means that the task will never run again.
Setting Next_date to NULL is a very easy way to do this if you want to keep the task in the task queue and do not want it to run.
Next_date can also be set to a time in the past. The order in which the system tasks are performed is determined based on their next execution time.
So setting the Next_date parameter to the past can achieve the purpose of scheduling the task before the task queue.

4) Interval
The internal parameter is a string that represents an Oracle legal date expression.
The value of this date string is calculated each time the task is executed.
It is emphasized that next_date is calculated at the beginning of a task, not when the task is successfully completed.
There are two possibilities for the calculated date expression, either the future time or null.
When the task completes successfully, the system updates the Task Queue table and resets the time that the next task is to run to the Next_date value previously calculated.
When the interval expression calculates that the next_date is null, the task is automatically removed from the task queue, and no further execution occurs.
Therefore, if you pass a null value to the interval parameter, the task is executed only once.


job_queue_processes
Oracle has a dedicated background process to perform task queues.
The number of background processes is set by job_queue_processes and can be set to 0 to 1000.
The *job_queue_interval has been abandoned since 9i.

Be aware that if you configure up to 1 000 jnnn processes, you will not see that there are really 1 000 processes starting with the database.
Instead, it starts with only one process, the Job Queue Coordinator (CJQ0),
It starts the jnnn process when it sees a job that needs to be run in the Job queue table.
If the JNNN process completes its work and discovers that there are no new jobs to process, the jnnn process exits.
Ps-ef can be used under Unix | grep ora_j View these processes (J000, ...). J999),
There is also a ora_cjq0_xxx queue management process.

When the Job Queue Coordinator (CJQ0) process is awakened,
It first looks at whether all of the tasks in the Task Queue directory are currently in excess of the next run's date and time.
After a task is detected that requires immediate execution of the time, the tasks are executed sequentially in the order of the next execution date.
When you start a task, the procedure is as follows: Start a new database session with the user name of the task owner. Change the session NLS settings to match the tasks that are currently ready when the task is first submitted or modified the last time. Calculates the next execution time by interval the date expression and the system time. Pl/sql execution of a task definition if the operation succeeds, the next execution date of the task (Next_date) is updated, otherwise the failure count plus 1.

Retry after a task has failed to run
The submitted task will attempt to run the task again after 2 minutes if it fails to run.
If this operation fails again, the next attempt will take place in 4 minutes, and then 8 minutes later.
The task queue doubles the retry interval each time until it exceeds the normal run interval.
So if the task's run interval is set relatively short, such as 1 minutes, this retry interval is not doubled.
After 16 consecutive failures, the task is marked as interrupted (broken) and the task is no longer repeated.
At this time, if the task is restarted through the broken or run process, the failure interval will be emptied after the successful trip.
If the reboot fails, then the number of failures becomes 17,job state back to broken.

Related Data dictionary
Task information in the task queue can be viewed in several dictionary views.

View Name	Describe
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

Fields in Dba_jobs and User_jobs.

Fields (Columns)	Type	Describe
JOB	Number	Unique indicator number of the task
Log_user	VARCHAR2 (30)	The user who submitted the task
Priv_user	VARCHAR2 (30)	The user who gives the task permission
Schema_user	VARCHAR2 (30)	User mode for parsing a task
Last_date	DATE	The last time the task was successfully run
Last_sec	VARCHAR2 (8)	Hours, minutes, and seconds of the last_date date, such as HH24:MM:SS format
This_date	DATE	The start time of the task that is running, or null if the task is not running
This_sec	VARCHAR2 (8)	Hours, minutes, and seconds of the this_date date, such as HH24:MM:SS format
Next_date	DATE	The time the next scheduled task runs
Next_sec	VARCHAR2 (8)	Hours, minutes, and seconds of the next_date date, such as HH24:MM:SS format
Total_time	Number	The total time required for the task to run, in seconds
Broken	VARCHAR2 (1)	Flag parameters, Y indicates that the task is interrupted and will not run later
INTERVAL	VARCHAR2 (200)	An expression used to calculate the next run time
Failures	Number	The number of times a task runs without success
WHAT	VARCHAR2 (2000)	Pl/sql block to perform a task
Current_session_label	RAW MLSLABEL	The trusted Oracle session character 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 a task to run
Misc_env	RAW (32)	Some other session parameters that the task runs

Dba_jobs_running's Field meaning

Column	Data type	Describe
Sid	Number	The session ID of the task that is currently running
JOB	Number	Unique identifier of the task
Failures	Number	Cumulative number of consecutive unsuccessful executions
Last_date	DATE	Date of last successful execution
Last_sec	VARCHAR2 (8)	Hours, minutes, and seconds of the last_date date, such as HH24:MM:SS format
This_date	DATE	The start date of the task that is currently running
This_sec	VARCHAR2 (8)	Hours, minutes, and seconds of the this_date date, such as HH24:MM:SS format

The interval between the

task run interval specified
Task runs repeatedly depends on the date expression set in the interval parameter. The simplest requirement for

is to run the task repeatedly after a specific time interval. For example, run ' sysdate+1 ' once a day.
This may cause problems that do not guarantee the exact time of the next run of the task. The
, for example, runs at 12 o'clock midnight for the first time, so that each run is theoretically 12 o'clock at midnight.
But if a user uses the run process to perform the task at 8 o'clock in the morning, the task is scheduled to run again until 8 o'clock in the morning.
In addition, 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,
can run a continuous "drift" of time. Another requirement for

is that the task needs to be executed at a specific time, and here are some examples.
#每天午夜12点 ' TRUNC (sysdate + 1) '
#每天早上8点30分 ' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '
#每星期二中午12点 ' Next_day (trun C (sysdate), ' Tuesday ') + 12/24 '
#每个月第一天的午夜12点 ' TRUNC (Last_day (sysdate) + 1) '
#每季度的第一天凌晨1点   ' TRUNC (add_m Onths (sysdate,3), ' Q ') + 1/24 '
#每个季度最后一天的晚上11点 ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
#每年1月1日凌晨1点执行 ' a Dd_months (Trunc (sysdate, ' yyyy '), +1/24 '
#每星期六和日上午11点   ' trunc (least (Next_day), sysdate,7 ( sysdate,1)) + (11*60)/(24*60) '
   Note: 1-Sunday 2-Week 1 3-Week 2 ...  7-Week 6