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

Today, the editor will share with you the relevant knowledge points about how to solve the failure of Oracle database JOB. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

Database version: 11.2.0.3

Test description: a log table and an error-bound procedure are created for running job. Here, as long as you record the next_date in the view user_jobs each time the job is executed, you can infer the retry rule after the job execution fails.

To test the retry rule of job, I did the following work

Log table and sequence:

Create table job_exec_logs (id number, current_date date, next_date date, failures number, broken varchar2 (2))

Create sequence seq_job_exec_logs_id

Test procedure

Create or replace procedure pro_my_test is

Begin

Insert into job_exec_logs select

Seq_job_exec_logs_id.nextval, sysdate, next_date, failures, broken from user_jobs

Commit

Execute immediate 'select * from ddddsfs'

End

Where ddddsfs means it does not exist, which means that as long as you run the pro_my_test stored procedure, there will be an error at the end (but the log table can still be inserted normally)

Create a job:

Var job number

Begin

Sys.dbms_job.submit (job = >: job

What = > 'pro_my_test;'

Next_date = > sysdate

Interval = > 'sysdate+5')

Commit

End

/

In fact, there is nothing to say about the whole testing process, just let job run it automatically, but you need to wait for job to retry next time.

The results of the final test:

Dexter@REPO > select trunc ((next_date-lag (next_date,1) over (order by 4)) * 24060) from (

2 select * from job_exec_logs

3 union all

4 select 11111, sysdate, next_date, failures, broken from user_jobs

5 order by 4 nulls first)

TRUNC ((NEXT_DATE-LAG (NEXT_DATE,1) OVER (ORDER BY 4)) * 24060)

two

four

eight

fifteen

thirty-two

sixty-four

one hundred and twenty eight

two hundred and fifty six

five hundred and twelve

1024

1440

1440

13 lines have been selected.

As shown in the above results and other tests by the author, we can judge that:

1, each retry time is increasing, the first time 2 minutes, 4 minutes, 8 minutes, 16 minutes. And so on.

2. When more than 1440 minutes, that is, 24 hours, the fixed retry time is 1 day.

3. Through other experiments, the author knows that after more than 16 retries, job will be marked as broken, and the next_date will be 4000-1-1, that is, no more job retries.

4. When the retry time of the oracle database reaches the set next execution time, the set job execution time shall prevail.

These are all the contents of this article entitled "how to solve the failure of JOB in Oracle Database". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.