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

Today, I will talk to you about the reason for the abnormal interruption of JOB in Oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

JOB Information:

Parameters:

BROKEN: interrupt flag,'N start, Y interrupt'- > DBMS_JOBS.BROKEN (job_id,TRUE/FALSE); stop / start job, then COMMIT; is required otherwise the setting is invalid

FAILURES: number of errors

Time of the last successful execution of last_date:job

Time of the next execution of next_date:job (affected by last_date and interval)

Total time for total_time:job to run (cumulative time for each run)

Time that this_date:job is executing (value if job is executing at the time of query)

Analysis:

* if there is an exception in the what stored procedure call of a timing task (for example, insufficient space, problems with the writing of the stored procedure itself, etc.)

* job will automatically try to change the broken status to Y after 16 consecutive failures, and change next-date to "4000-1-1". You can only manually exec dbms_job.run (: id) to start job.

For example, stored procedure p1, comment begin/end lines casually to reflect syntax problems, job will normally run 16 times according to the job of p1 stored procedure, and then broken interrupts job

If the stored procedure is changed to the correct state up to 10 times, the number of errors will be changed to zero and the error number will be changed to normal

The method of judging the problem:

1. Manual call storage to determine what the problem is. If no error is reported during call storage, there is a problem with the storage before. Job has tried to run it more than 16 times, and manual exec dbms_job.run (: id) is required; start job

2. Check the alert log, and the oracle job exception will be recorded in the alarm log.

Try to start.

SQL > begin 2 dbms_job.run (1543); 3 end; 4 / ORA-12011: unable to execute 1 job ORA-06512: in "SYS.DBMS_IJOB", line 648 ORA-06512: in "SYS.DBMS_JOB", line 284 ORA-06512: in line 2 SQL >

If an error is reported, take a look at the content of alert (solution 2):

1 、

ORA-12012: automatic execution job 1543 error ORA-12899: column "FWS". "RECODE_ERROR_MSG". "ERROR_MSG" is too large (actual value: 704, * * value: 500) ORA-06512: in "FWS.PROC_WRITEERRMSG", line 22 ORA-06512: in "FWS.PKG_WMS" Line ORA-01688: table FWS.RECODE_ERROR_MSG partition SYS_P6181 cannot be extended through 8192 (in tablespace TBS_WMS_CITY_JK_DATA) ORA-06512: in "FWS.PROC_WRITEERRMSG", line 22 ORA-06512: in "FWS.PKG_WMS" Line 514 ORA-01688: table FWS.RECODE_ERROR_MSG partition SYS_P6181 cannot be extended through 8192 (in tablespace TBS_WMS_CITY_JK_DATA) ORA-06512: in "FWS.PROC_WRITEERRMSG", line 22 ORA-06512: in "FWS.PKG_WMS" Line 502 ORA-01400: cannot insert NULL into ("FWS". "BILL_RECEIPT_CITY". "CREATOR") ORA-06512: in line 1 ORA-1688: unable to extend table FWS.RECODE_ERROR_MSG partition SYS_P6181 by 128 in tablespace TBS_WMS_CITY_JK_DATA ORA-1688: unable to extend table FWS.RECODE_ERROR_MSG partition SYS_P6181 by 8192 in tablespace TBS_WMS_CITY_JK_DATA ORA-1688: unable to extend table FWS.RECODE_ERROR_MSG partition SYS_P6181 by 128 in tablespace TBS_WMS_CITY_JK_DATA ORA-1688: unable to extend table FWS.RECODE_ERROR_MSG partition SYS_P6181 by 8192 in tablespace TBS_WMS_CITY_JK_DATA

2 、

ORA-12012: automatic execution Job 26 error ORA-06550: line 1, column 96: PLS-00905: object LOTTERY.P_LOCK_CHECK_HD invalid ORA-06550: line 1, column 96: PL/SQL: Statement ignored Mon Jul 13 14:39:55 2015 Errors in file / u01/app/oracle/diag/rdbms/bjcc/ccem01/trace/ccem01_j001_36869.trc:

Just follow alert log to solve the problem.

* 1. The shortage of space is due to the increase of 100 million data in the RECODE_ERROR_MSG table every day, which leads to.. truncate table RECODE_ERROR_MSG. Analysis on the reasons for the surge of Oracle Archives

* 2. Your own test will store the error information of setting failure.

After reading the above, do you have any further understanding of the reason for the abnormal interruption of JOB in Oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.