Oracle Scheduled Tasks

First, Introduction
When we need an Oracle database to automate some scripts, or to perform database backups, database performance optimizations, including rebuilding indexes, you need to use a timed task.
Scheduled tasks can be completed using the following two types.
1, operating system-level timing tasks, WIN's scheduled Tasks, Unix crontab
2, the database level of scheduled tasks.
In contrast, the database's timing task is more efficient.
Scheduled Tasks for Oracle: When using Oracle timed tasks, you need to use the Dbms_job.submit function in Oracle.

Common parameters of the dbms_job process

Unique identification number for job Binary_integer task
What VARCHAR2 as the PL/SQL code for Task execution
Next_date VARCHAR2 task Next Run time
Interval VARCHAR2 date expression used to calculate the time of the next task run

1. Job Parameters
A job is an integer that uniquely identifies a task. This parameter can be assigned either by the user or automatically by the system, depending on which task submission process was selected when the task was submitted. 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 identify the submitted task. and Dbms_job. The Isubmit procedure assigns a recognition number to the task by the caller, and the uniqueness of the task number depends entirely on the caller. In addition to deleting or resubmitting a task, the task number is generally not changed. Even when the database is exported or imported, the task number is preserved. Therefore, the task number conflict is likely to occur when you perform an import/export operation that contains data for the task.

2. What
What parameter is a string that can be converted to a legitimate PL/SQL call that will be executed automatically by the task queue. In the What parameter, if a literal string is used, the string must be enclosed in single quotation marks. What parameter can also use the VARCHAR2 variable that contains the string value we need. The actual PL/SQL calls must be separated by semicolons. In a PL/SQL call, if you want to embed a literal string, you must use two single quotation marks. The length of what parameter is limited to 2000 bytes in Oracle7.3, and after Oracle 8.0 it expands to 4,000 bytes, which is sufficient for general applications. The value of this parameter is typically a call to a PL/SQL stored procedure. The practical experience is that it is best to encapsulate a stored procedure call in an anonymous block, which avoids some errors. For example, in general, what parameter can be quoted as follows: what = ' my_procedure (parameter1); ' But a more secure reference should be written like this: what = ' Begin My_procedure ( Parameter1); End; '. At any time, we can change the purpose of the task definition by changing the What parameter. However, it is important to note that the task will run the same time as before by changing the What parameter to change the task definition. The new run time needs to be reset to achieve the purpose of the scheduled task.

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 default to the current time of the system, which means that the task will run immediately. When you assign null to the Next_date parameter of a task, the next time the task runs is specified as January 1, 4000, which means that the task will never run again. However, if you want to keep the task in the task queue and do not want it to run, you can set next_date to null. Next_date can also be set to a time in the past. The order in which the system tasks are executed is determined based on their next execution time, so setting the Next_date parameter back will allow the task to be queued before the task queue. This can be set when the task queue process cannot keep up with the task that will be performed or a specific task needs to be executed as soon as possible.

4, Interval
The interval parameter is a string representing the Oracle legal date expression. The value of this date string is calculated each time the task is executed, with two possible date expressions, either a future time or null. Next_date is calculated at the beginning of a task, not when the task is completed successfully. When the task completes successfully, the system resets the previously calculated Next_date value to the time the next task will run by updating the Task Queue catalog table. When the interval expression calculates that next_date is null, the task is automatically removed from the task queue and no further execution continues. Therefore, if you pass a null value to the interval parameter, the task executes only once. By assigning different values to the interval parameter, you can design the task of a complex run-time plan.

Ii. examples

Stored procedures are more complex tasks such as data integration in a database. For example, the banking system, data is very important, daily need to back up the data of important tables. There are more than 100,000 data per day, which requires a scheduled task to be backed up.

General ideas are as follows:
1. After the data is backed up every day, max (ID) is saved to a table
2. Select Max (ID) for the next backup;
3.select * FROM table where ID > max (ID);

Example code:

Back up records added in the person table once per minute

The person table structure is as follows:

CREATE TABLEPerson (ID Number( One) not NULL, usernameVARCHAR2(255)NULL, Age Number( One)NULL, PasswordVARCHAR2(255)NULL ,PRIMARY KEY(ID))

The Backup Table Person_back table structure is as follows:

CREATE TABLEPerson_back (ID Number( One) not NULL, usernameVARCHAR2(255)NULL, Age Number( One)NULL, PasswordVARCHAR2(255)NULL ,PRIMARY KEY(ID))

Auxiliary table TB_MAXID, which holds the largest ID in the current person table

Create Table  Number ); Insert  into Values (0);

The other code is as follows:

/*1. Extract person, all data 2, loop INSERT into Person_back table 3, record Max (ID) to TB_MAXID table main problem: 1, if the commit in the loop, the efficiency will be very low 2, the assumption that the data is very large (million) rollback segment is not enough (throw Exception).       The default size of the rollback segment can be set to 2.1, to increase the rollback segment. 2.2 Fragment Submission*/--stored procedures, saving person recordsCreate or Replace procedurePro_back_person (V_maxid Number) is   --Defining Cursors   cursorC_person is   Select *  fromPersonwhereId>V_maxid; --define RowTypeR_person person%RowType; --judging the number of cyclesV_index Number:= 0; --V_max Number;begin  --determine if the cursor is open  ifC_person%IsOpen Then --Cursor Open    NULL; Else   OpenC_person; End if; --Cycle Back DataLoopFetchC_person intoR_person; Exit  whenC_person%NotFound; Insert  intoPerson_backValues(R_person.id,r_person.username, R_person.age, R_person.password); --Staging SubmissionsV_index:=V_index+ 1; if(V_index=  -) Then       Commit; V_index:= 0; End if; EndLoop; Commit;--Submit data again    Select Max(ID) intoV_max fromPerson_back; UpdateTb_maxidSetId=V_max; Commit; --Close Cursors  CloseC_person;EndPro_back_person;--call a stored procedure that holds a recordCreate or Replace procedureInvokebackpro isV_maxid Number;begin  SelectId intoV_maxid fromTb_maxid; Pro_back_person (V_MAXID);EndInvokebackpro;--Create a task timerDeclareJobno Number;beginDbms_job.submit (jobno, what= 'Invokebackpro;',--Invokebackpro The name of the stored procedureInterval= 'TRUNC (Sysdate,"'mi"') +1/(24*60)' --Define event interval per minute  ); Commit;End;

Third, other relevant

Some common interval settings are as follows:

1, execute Interval per minute=TRUNC (Sysdate,'mi')+ 1/( -* -)2, daily scheduled execution (2 o'clock in the morning daily) Interval=TRUNC (Sysdate)+ 1 + 2/( -)3, scheduled weekly execution (every Monday 2 o'clock in the morning) Interval=TRUNC (Next_day (Sysdate,2))+ 2/( -)4, monthly scheduled Execution (1st 2 o'clock in the morning each month) Interval=TRUNC (Add_months (sysdate))+ 1 + 2/ -5, no quarterly scheduled execution (no jealous definition Day 2 o'clock in the morning execution) Interval=TRUNC (Add_months (Sysdate,3),'Q')+ 2/ -6, every half-yearly scheduled execution (July 1 and January 1 every year 2 o'clock in the morning) Interval=TRUNC (Add_months (Sysdate,'yyyy'),6)+ 2/ -7, scheduled execution every year (January 1 2 o'clock in the morning each year) Interval=TRUNC (Add_months (Sysdate,'yyyy'), A)+ 2/ -

The data dictionary for timers in Oracle is as follows:
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

Oracle Scheduled Tasks