Oracle scheduled task and oracle task

Oracle scheduled task and oracle task

I. Introduction
When we need the oracle database to automatically execute some scripts, or perform database backup, database performance optimization, including re-indexing, etc., we need to use the scheduled task.
Scheduled tasks can be completed in either of the following ways.
1. OS-level scheduled tasks, win scheduled tasks, and unix crontab
2. Database-level scheduled tasks.
Relatively speaking, the database's scheduled tasks are more efficient.
Oracle scheduled tasks: when using oracle scheduled tasks, you must use the dbms_job.submit function in oracle.

Common Parameters of the DBMS_JOB Process

Unique identification number of a Job BINARY_INTEGER
What VARCHAR2 is used as the PL/SQL code for task execution
Next_date VARCHAR2 task next run time
Interval VARCHAR2 date expression, used to calculate the time when the next task runs

1. job Parameters
A job is an integer that uniquely identifies a task. This parameter can be specified by the user or automatically assigned by the system, depending on which task is selected for submission. The DBMS_JOB.SUBMIT process automatically assigns a task number by obtaining the next value of the sequence SYS. JOBSEQ. This task number is returned as an OUT parameter, so the caller can identify the submitted task. In the DBMS_JOB.ISUBMIT process, the caller specifies a identification number for the task. At this time, the uniqueness of the task number depends entirely on the caller. Except for deleting or resubmitting a task, the task number cannot be changed. Even if the database is exported or imported, the task number is retained. Therefore, a task number conflict may occur during the import/export operation of data containing tasks.

2. what
What is a string that can be converted to a valid PL/SQL call. The call is automatically executed by the task queue. In the what parameter, if a text string is used, the string must be enclosed by single quotation marks. The what parameter can also use the VARCHAR2 variable that contains the required string value. Actual PL/SQL calls must be separated by semicolons. To embed a text string in a PL/SQL call, you must use two single quotes. The length of what parameter is limited to 2000 bytes in Oracle7.3 and 8.0 bytes after Oracle 4000, which is sufficient for general applications. The value of this parameter is generally a call to a PL/SQL stored procedure. In practice, it is best to encapsulate stored procedure calls in an anonymous block to avoid errors. For example, in general, the what parameter can be referenced as follows: what => 'my _ procedure (parameter1); 'but the reference is safer. It should be written as follows: what => 'in in my_procedure (parameter1); end ;'. At any time, we can change the task definition by changing the what parameter. However, when you change the definition of a task by changing the what parameter, the running time of the task will be the same as before. You need to reset the new running time to achieve the scheduled task.

3. next_date
The next_date parameter is used to schedule the next running time of the task in the task queue. This parameter defaults to the current system time for DBMS_JOB.SUBMIT and DBMS_JOB.BROKEN, that is, the task runs immediately. When the next_date parameter of a task is assigned null, the next running time of the task is set to January 1, 4000, that is, 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 previous time. The execution sequence of system tasks is determined based on their next execution time. Therefore, you can set the next_date parameter to rank the task before the task queue. This can be set when the task queue process cannot keep up with the task to be executed or a specific task needs to be executed as soon as possible.

4. Interval
The interval parameter is a string that represents a valid Oracle date expression. The value of this date string is calculated when each task is executed. The calculated date expression has two possibilities: either a future time or null. Next_date is calculated at the beginning of a task, rather than when the task is successfully completed. When the task is successfully completed, the system updates the task queue directory table to set the previously calculated next_date value to the time for the next task to run. When the value of next_date is null calculated by the interval expression, the task is automatically removed from the task queue and will not continue to be executed. Therefore, if a null value is passed to the interval parameter, the task is executed only once. By assigning different values to the interval parameter, you can design tasks with complex running time plans.

Ii. Example

The stored procedures are more complex tasks such as data integration on the database side. For example, in the banking system, data is very important and data on important tables needs to be backed up every day. There are more than 10 million data records every day. At this time, you need to use a scheduled task for backup.

The general idea is as follows:
1. After 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 );

Sample Code:

Backs up the added records in the person table once per minute.

The structure of the person table is as follows:

CREATE TABLE person(id NUMBER(11) NOT NULL ,username VARCHAR2(255 ) NULL ,age NUMBER(11) NULL ,password VARCHAR2(255) NULL ,PRIMARY KEY (id))

The structure of the backup table person_back is as follows:

CREATE TABLE person_back (id NUMBER(11) NOT NULL ,username VARCHAR2(255 ) NULL ,age NUMBER(11) NULL ,password VARCHAR2(255) NULL ,PRIMARY KEY (id))

Auxiliary table tb_maxid, used to store the largest id in the current person table

create table tb_maxid(id number);insert into tb_maxid values(0);

Other code is as follows:

/* 1. Extract person, all data. 2. Insert data to the person_back table cyclically. 3. Record max (id) to the tb_maxid table after completion. Main problems: 1. If commit is in the loop, the efficiency will be very low. 2. Assume that the rollback segment is too large (tens of millions of data records) (throwing an exception ). The default size of a rollback segment can be set to 2.1 or greater. 2.2 multipart commit */-- stored procedure, save the create or replace procedure pro_back_person (v_maxid number) Record of person is -- Define the cursor c_person is select * from person where id> v_maxid; -- defines rowtype r_person person % rowtype; -- determines the number of cycles v_index number: = 0; -- v_max number; begin -- determines whether the cursor opens if c_person % isopen then -- if the cursor opens null; else open c_person; end if; -- loop backup data loop fetch c_person into r_person; exit when c_person % notfound; insert into person_back values (r_person.id, r_person.username, r_person.age, r_person.password ); -- submit v_index: = v_index + 1; if (v_index = 2000) then commit; v_index: = 0; end if; end loop; commit; -- re-submit the data select max (id) into v_max from person_back; update tb_maxid set id = v_max; commit; -- close the cursor close c_person; end pro_back_person; -- call the stored procedure create or replace procedure invokebackprois v_maxid number; begin select id into v_maxid from tb_maxid; pro_back_person (v_maxid); end invokebackpro; -- create task timer declare jobno number; begin dbms_job.submit (jobno, what => 'invokebackpro; ', -- invokebackpro is the name of the stored procedure Interval => 'trunc (sysdate, 'mi '') + 1/(24*60) '-- defines the event interval every minute); commit; end;

Iii. Other problems

Some common Interval Settings are as follows:

1. Execute Interval => TRUNC (sysdate, 'mi') + 1/(24*60) every minute. 2. Execute regularly every day (every day) interval => TRUNC (sysdate) + 1 + 2/(24) 3. scheduled weekly execution (every Monday) Interval => TRUNC (next_day (sysdate, 2 )) + 2/(24) 4. scheduled monthly execution (executed at on the first day of each month) Interval => TRUNC (ADD_MONTHS (sysdate )) + 1 + 2/245. No quarterly scheduled execution (no jealous definition day executed at AM) Interval => TRUNC (ADD_MONTHS (sysdate, 3), 'q ') + 2/246. scheduled execution every six months (on July 15 and July 15) Interval => TRUNC (ADD_MONTHS (sysdate, 'yyyy'), 6) + 2/247. scheduled execution every year (executed at on January 1, January 1) Interval => TRUNC (ADD_MONTHS (sysdate, 'yyyy'), 12) + 2/24

The timer data dictionary in oracle is as follows:
DBA_JOBS tasks defined in the task queue in this database
DBA_JOBS_RUNNING
USER_JOBS tasks owned by the current user

Oracle scheduled task

DBMS_JOB.SUBMIT (
Job OUT BINARY_INTEGER,
What IN VARCHAR2, NEXT_DATE in date defaultsysdate,
Interval IN VARCHAR2 DEFAULT 'null ',
No_parse in boolean default false,
Instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
Force in boolean default false );

What is the PL/SQL code that needs to be called regularly
Interval indicates the next job running time.

Oracle scheduled task

DBMS_JOB.SUBMIT (
Job OUT BINARY_INTEGER,
What IN VARCHAR2, NEXT_DATE in date defaultsysdate,
Interval IN VARCHAR2 DEFAULT 'null ',
No_parse in boolean default false,
Instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
Force in boolean default false );

What is the PL/SQL code that needs to be called regularly
Interval indicates the next job running time.