Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand Oracle job timing tasks". In daily operation, I believe many people have doubts about how to understand Oracle job timing tasks. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to understand Oracle job timing tasks". Next, please follow the editor to study!

Oracle job scheduled task

For the task queue to work properly, it must also start its own proprietary background process with the following parameters:

JOB_QUEUE_PROCESSES = n

This is the number of processes running JOB. Of course, when the JOB in the system is greater than this value, there will be queues. The minimum value is 0, which means that JOB is not running, and the maximum value is 1000. When the corresponding process on OS, the process that manages JOB on SNPn,9i later on OS is called CJQn.

You can use the following SQL to determine how many SNP/CJQ are currently running.

SQL > set lines 200

SQL > select * from v$bgprocess where name like 'CJQ%'

PADDR PSERIAL# NAME DESCRIPTION ERROR

-

C0000005F4610F38 1 CJQ0 Job Queue Coordinator #

The snp/cjq process whose paddr is not empty is a currently idle process, and some indicate that it is working.

3. Parameters of DBMS_JOB package

All procedures in the DBMS_JOB package have the same set of common parameters that define the task, the running time of the task, and the interval at which the task runs regularly. These common task definition parameters are shown in Table 2.

Let's discuss the meaning and usage of these parameters in detail.

1 、 job

The parameter job is an integer that uniquely identifies a task. This parameter can be specified by the user or automatically given by the system, depending on which task submission process is selected when the task is submitted. The DBMS_JOB.SUBMIT process automatically assigns a task number by getting the next value of the sequence SYS.JOBSEQ. The task number is returned as an OUT parameter, so the caller can then identify the submitted task. In the DBMS_JOB.ISUBMIT process, the caller assigns an identification number to the task, so the uniqueness of the task number depends entirely on the caller.

In general, the task number cannot be changed except to delete or resubmit the task. Even in such extreme cases as the database is exported or imported, the task number will be retained. Therefore, task number conflicts are likely to occur when performing import / export operations of data containing tasks.

2 、 what

The what parameter is a string that can be converted into a legitimate PL/SQL call, which is automatically executed by the task queue. In the what parameter, if you use a literal string, the string must be enclosed in single quotation marks. The what parameter can also use a VARCHAR2 variable that contains the string values we need. Actual PL/SQL calls must be separated by semicolons. If you want to embed a literal string in a PL/SQL call, you must use two single quotes.

The length of the what parameter is limited to 2000 bytes in Oracle7.3, and has been expanded to 4000 bytes after Oracle 8.0, which is sufficient for general applications. The value of this parameter is usually a call to a PL/SQL stored procedure. In practical applications, although large anonymous Pl/SQL blocks can be used, it is recommended that you do not use them in this way. Another practical experience is that it is best to encapsulate stored procedure calls in an anonymous block to avoid some more inexplicable errors. Let me give you an example. In general, the what parameter can be referenced as follows:

What = > 'my_procedure (parameter1);'

But for a safer reference, it should be written like this:

What = > 'begin my_procedure (parameter1); end;'

At any time, we can change the task definition simply by changing the what parameter. It is important to note, however, that when you change the task definition by changing the what parameter, the user's current session settings are also recorded and become part of the task's running environment. If the current session settings are different from the session settings when the task was originally submitted, it is possible to change the running behavior of the task. It's important to be aware of this potential side effect and make sure that the session is set correctly whenever you apply the what parameter in any DBMS_JOB procedure.

3 、 next_date

The Next_date parameter is used to schedule the next run of the task in the task queue. This parameter defaults to the current system time for both DBMS_JOB.SUBMIT and DBMS_JOB.BROKEN processes, which means that the task will run immediately.

When a task's next_date parameter is assigned to null, the next time the task runs will be specified as January 1, 4000, meaning the task will never run again. In most cases, this may be something we don't want to see. On the other hand, if you want to keep the task in the task queue but don't want it to run, setting next_date to null is a very simple way.

Next_date can also be set to a time in the past. Note here that the execution order of system tasks is determined according to their next execution time, so setting the next_date parameter back can achieve the goal of placing the task in front of the task queue. This is useful when the task queue process cannot keep up with the task to be executed and a particular task needs to be executed as soon as possible.

4 、 Interval

The Internal parameter is a string that represents a legal date expression for Oracle. The value of this date string is calculated each time the task is executed, and there are two possible date expressions, either a future time or null. To emphasize here: many developers do not realize that next_date is calculated at the beginning of a task, not when the task is successfully completed.

When the task completes successfully, the system sets the previously calculated next_date value to the time the next task will run by updating the task queue catalog table. When it is calculated by the interval expression that next_date is null, the task is automatically removed from the task queue and execution does not continue. Therefore, if you pass a null value to the interval parameter, the task is executed only once.

Complex run-time scheduled tasks can be designed by assigning different values to the interval parameter. The interval expression is discussed in detail in "Task interval and date algorithm" later in this article, and an example of practical use of interval expression is given.

IV. Task queue architecture and operating environment

When the CJQn process wakes up, it first checks to see if the current time of all tasks in the task queue directory exceeds the date time of the next run. When the CJQn process detects tasks that need to be executed immediately at that time, they are executed in the order of the next execution date. When the CJQn process starts to execute a task, the process is as follows:

1. Start a new database session with the user name of the task owner.

two。 When a task is first committed or last modified, changing the session NLS settings matches the currently ready task.

3. The next execution time is calculated through the interval date expression and the system time.

4. Execute the PL/SQL defined by the task

5. If the run is successful, the next execution date (next_date) of the task is updated, otherwise, the failure count is incremented by 1.

6. After JOB_QUEUS_INTERVAL seconds, it's time for another task to run, repeating the above process.

In the first two steps, the CJQn process creates a session environment that mimics the PL/SQL defined by the user running the task. However, the running environment of this imitation is not exactly the same as the actual session environment of the user, so you need to pay attention to the following two points:

First, any non-provincial roles that are available when the task is submitted will not be available in the task runtime environment. Therefore, tasks that want to obtain permissions from non-provincial roles cannot be submitted, and user-defined roles can be modified dynamically during the future run of the task.

Second, any database join required by the task definition itself or during process execution must fully satisfy the remote user name and password. The CJQn process cannot initiate a remote session without an explicit password. Obviously, the CJQn process cannot assume that the local user's password is part of the session setting in the remote runtime environment.

When a task fails, the CJQn process will try to run the task again after 1 minute. If this run fails again, the next attempt will be made in 2 minutes, and the next attempt will be in 4 minutes. The task queue doubles the retry interval each time until it exceeds the normal running interval. After 16 consecutive failures, the task is marked as broken, and without user intervention, the task queue will not be repeated.

Task queue dictionary table and view

The task information in the task queue can be viewed through several dictionary views shown in Table 3, which are created by the CATJOBQ.sql script. Tables 4 and 5 show the meaning of each field in each view.

Sixth, the design algorithm of task repetition interval.

The interval at which tasks are repeated depends on the date expression set in the interval parameter. Let's talk in detail about how to set the interval parameter to accurately meet the requirements of our task. Generally speaking, there are three timing requirements for the scheduled execution of a task.

1. Run the task repeatedly after a specific interval.

two。 Run the task at a specific date and time.

3. After the task completes successfully, the next execution should be after a specific time interval.

The first date algorithm for scheduling task requirements is relatively simple, that is, 'SYSDATE+n', where n is a time interval in days. Table 6 shows some examples of this interval setting.

The task interval expression shown in Table 6 does not guarantee that the next run time of a task will be on a specific date or time, but can only specify the interval between two runs of a task.

For example, if a task is first run at 12:00 and interval is specified as' SYSDATE + 1', the task is scheduled to execute at 12:00 the next day. However, if a user performs the task manually (DBMS_JOB.RUN) at 4 p.m., the task will be rescheduled to 4 p.m. The next day. Another possible reason is that if the database is closed or the task queue is so busy that the task cannot be executed on time at the planned point in time. In this case, the task will try to run as soon as possible, that is, as soon as the database is opened or the task queue is not busy, but by this time, the run time has drifted from the original commit time to the real run time later. This constant "drift" of the next run time is a typical feature of a simple time interval expression.

The second scheduling task requirement requires a more complex time interval (interval) expression than the first. Table 7 shows some examples of interval settings that require tasks to be run at a specific time.

The third kind of scheduling task requirements no matter how to set the interval date expression can not meet the requirements. At this point, because the next run time of a task is calculated at the beginning of the task, and at this time it is not known when the task will end.

What should I do in such a situation? Of course, there must be a way, and we can do it by writing procedures for the task queue. Here I just briefly introduce the following, you can get the system time of the task completion during the execution of the previous task queue, and then add the specified time interval to control the next task to be executed. There is a prerequisite that the currently running task itself must strictly adhere to its own schedule.

VI. Overview of the experiment

At present, all the popular mainstream databases have this function, the most representative of which are Microsoft SQL Server 7.0and Oracle8i/9i. However, for Job to work, we still need to configure it. These configurations all have GUI operations. This article introduces the implementation of Job configuration through the command line after Oracle9i.

As we all know, the general operating system will provide the method to execute tasks regularly, for example, the command Crontab is provided on the Unix platform to allow the system to execute tasks regularly. However, for some requirements, such as some operations on database tables, the most typical is the daily closing settlement of the stock exchange, which involves a large number of database table operations. If the operating system is still used to execute regularly, not only a lot of programming work is required, but also there will be running errors such as inconsistent users, and even lead to the program can not be executed.

In fact, for the above requirements, we can use the database itself has the function of Job Queue (task queue manager) to achieve. Job allows users to schedule and schedule a task in advance so that it can automatically execute one or more times in a specified point or period of time. Because the task is executed in the database, it is very efficient.

Job allows us to customize the execution time of tasks, and provides flexible processing methods. Through configuration, tasks can be arranged to be executed in a period of time when the number of users of the system is low, which greatly improves the work efficiency. For example, for the time-consuming and repetitive work such as database backup, update, deletion and replication, as well as the timing PUSH in telecom value-added short message service, we can use Job to automatically execute to reduce the workload.

At present, all the popular mainstream databases have this function, the most representative of which are Microsoft SQL Server 7.0and Oracle8i/9i. However, for Job to work, we still need to configure it. These configurations all have GUI operations. This article introduces the implementation of Job configuration through the command line after Oracle9i.

Premise: a stored procedure that is written to be executed regularly [without parameters].

Defining a Job, the execution interval is an important thing to pay attention to. A SYSDATE+1/24 is an interval stored in the dba_jobs view that produces hourly snapshots. You can change this data to a different sampling time, which is 24 minutes 60 minutes in a day, and you can use this number to adjust the number of execution times. For example, if I want to take a snapshot every 10 minutes, I should use the following command:

Execute dbms_job.submit (

: jobno,-- Job number

'sp;',-- the process of execution

Trunc (sysdate+10/1440,'MI')-- next execution time

'trunc (sysdate+10/1440,''MI'')'-- interval

True-- no_parse

: instno)

1. Create JOB

Create a task at intervals of 5 minutes.

Variable v_sn number

Begin

Dbms_job.submit (: v_sn

'paired pushy sendforth'

Trunc (sysdate+5/1440,'MI')

'trunc (sysdate+5/1440,''MI'')')

Commit

End

/

Delete a task:

Execute dbms_job.remove (jobno)

two。 Query task statement

Two tables are involved: dba_jobs and dba_jobs_running

Select * from dba_jobs

Select * from dba_jobs_running

Select job,what,to_char (last_date,'yyyy-mm-dd HH24:mi:ss'), to_char (next_date,'yyyy-mm-dd HH24:m), interval from dba_jobs where job in (325295)

Select job,what,last_date,next_date,interval from dba_jobs where job in (1pr 3)

3. Necessary parameters [modify initsid.ora parameters]

Job_queue_processes = 4-number of executable jobs

Job_queue_interval = 10-the default interval delay time is 10s

Job_queue_keep_connections=true-- job maintains normal connection

Modify the number of executable jobs to 20:

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 2bot 0

Modify the unrestrict mode:

ALTER SYSTEM DISABLE RESTRICTED SESSION

4. Several related Job operations

Delete job:

Exec dbms_job.remove (jobno)

Modify the action to be performed:

Exec dbms_job.what (jobno,what)

Modify the next execution time:

Exec dbms_job.next_date (job,next_date)

Modification interval:

Exec dbms_job.interval (job,interval)

Stop job:

Exec dbms.broken (job,broken,nextdate)

Start job:

Exec dbms_job.run (jobno)

5. About the delay of Job

Job has varying degrees of delay, to completely eliminate this error, it is best to use the operating system timer crontab or at, , just kidding.

A the problem of time overlap [netizens]:

For example, I have two JOB, both of which run at 3: 00 in the morning, so if one of them runs at 3: 00, the other must wait for the first JOB to finish before it can be executed. Especially some like sysdate+1/24, so the JOB that runs every other hour is more likely to conflict; even if there is no time overlap, ORACLE checks the JOB queue at the interval of job_queue_internal (usually 1 minute), so that the 1 o'clock job is checked at exactly 3:00:45, so the job is not executed until 3:00:45. We know that if the ORACLE JOB fails for the first time, it starts the JOB again at regular intervals until it succeeds, and if it fails until 16 times, the JOB is interrupted, so the actual running time is delayed.

B uses the "precise timing" function (as you can see earlier, it is very difficult to implement precise timing to execute JOB)

We can use things such as trunc (sysdate) + (1x 24) / 25 or trunc (sysdate) + 25Accord 24. Indicates that the job is executed at 1 o'clock every day, so that it will not be affected by the last JOB delay. It has not been tested.

6. A simple example:

6.1 create a test table

SQL > create table T (a date)

The table has been created.

6.2 create a custom process

SQL > create or replace procedure MYPROC as

Begin

Insert into T values (sysdate)

End

/

The procedure has been created.

6.3 create JOB

SQL > variable job1 number

SQL > begin

Dbms_job.submit (: job1,'MYPROC;',sysdate,'sysdate+1/1440');-- run the test process 1440 minutes a day, that is, once a minute

End

/

The PL/SQL process completed successfully.

SQL > print job1

JOB1

-

three

6.4 run JOB

SQL > begin

Dbms_job.run (: job1)

End

/

The PL/SQL process completed successfully.

SQL >

-Verification

SQL > select to_char time from T

time

-

2017-11-09 14:54:14

SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

SQL > select sysdate from dual

SYSDATE

-

2017-11-09 14:56:05

SQL > select to_char time from T

time

-

2017-11-09 14:54:14

2017-11-09 14:55:14

SQL > select to_char time from T

time

-

2017-11-09 14:54:14

2017-11-09 14:55:14

2017-11-09 14:56:14

SQL >

6.5 Delete JOB

SQL > begin

Dbms_job.remove (: job1)

End

/

The PL/SQL process completed successfully.

6.6 Note for creating DBMS_JOB.submit:

Why can't the newly created JOB be executed automatically? This is the result of an oversight!

When you create a JOB, you need to specify "COMMIT;" at the end! Indicates that it will be executed once after the creation is completed.

Delete the previous JOB and recreate a new JOB with the "COMMIT" statement.

SQL > variable job1 number

SQL > begin

Dbms_job.submit (: job1,'MYPROC;',sysdate,'sysdate+1/1440')

Commit

End

/

PL/SQL procedure successfully completed.

SQL >

SQL > print job1

JOB1

-

four

The JOB information created this time is as follows. You can see that the LAST_DATE has content after it has been created, indicating that it has been executed once.

SQL > col log_user for A10

SQL > col INTERVAL for A30

SQL > col what for A30

SQL > select job,log_user,to_char (last_date,'yyyy-mm-dd hh34:mi:ss') last_date,to_char (next_date,'yyyy-mm-dd hh34:mi:ss') next_date,interval, what from user_jobs

JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL WHAT

-

4 SYS 2017-11-09 15:02:34 2017-11-09 15:03:34 sysdate+1/1440 MYPROC

SQL >

SQL > select to_char time from T

time

-

2017-11-09 14:54:14

2017-11-09 14:55:14

2017-11-09 14:56:14

2017-11-09 14:57:14

2017-11-09 14:58:14

2017-11-09 14:59:14

2017-11-09 15:00:32

2017-11-09 15:00:34

2017-11-09 15:01:34

2017-11-09 15:02:34

10 rows selected.

6.7 run the previous uncommit dbms_job.SUBMIT package again

Delete first:

SQL > begin

Dbms_job.remove (: job1)

End

/

PL/SQL procedure successfully completed.

SQL > select to_char time from T

time

-

2017-11-09 14:54:14

2017-11-09 14:55:14

2017-11-09 14:56:14

2017-11-09 14:57:14

2017-11-09 14:58:14

2017-11-09 14:59:14

2017-11-09 15:00:32

2017-11-09 15:00:34

2017-11-09 15:01:34

2017-11-09 15:02:34

2017-11-09 15:03:34

time

-

2017-11-09 15:04:34

2017-11-09 15:05:34

2017-11-09 15:06:34

2017-11-09 15:07:34

15 rows selected.

SQL > select job,log_user,to_char (last_date,'yyyy-mm-dd hh34:mi:ss') last_date,to_char (next_date,'yyyy-mm-dd hh34:mi:ss') next_date,interval, what from user_jobs

No rows selected

-- run dbms_job.submit:

SQL > variable job1 number

SQL > begin

Dbms_job.submit (: job1,'MYPROC;',sysdate,'sysdate+1/1440')

End

/

PL/SQL procedure successfully completed.

SQL > print job1

JOB1

-

five

-- query user_jobs:

SQL > select job,log_user,to_char (last_date,'yyyy-mm-dd hh34:mi:ss') last_date,to_char (next_date,'yyyy-mm-dd hh34:mi:ss') next_date,interval, what from user_jobs

JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL WHAT

-

5 SYS 2017-11-09 15:08:34 sysdate+1/1440 MYPROC

SQL >

SQL > select to_char time from T

time

-

2017-11-09 14:54:14

2017-11-09 14:55:14

2017-11-09 14:56:14

2017-11-09 14:57:14

2017-11-09 14:58:14

2017-11-09 14:59:14

2017-11-09 15:00:32

2017-11-09 15:00:34

2017-11-09 15:01:34

2017-11-09 15:02:34

2017-11-09 15:03:34

time

-

2017-11-09 15:04:34

2017-11-09 15:05:34

2017-11-09 15:06:34

2017-11-09 15:07:34

15 rows selected.

SQL >

Note: the LAST_DATE content here is empty, indicating that the JOB has not been executed, so the JOB will never be executed automatically.

So, how do you make it execute automatically? It's simple, as long as we execute the JOB manually.

At this point, the study on "how to understand Oracle job scheduled tasks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.