Database Error
GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file/opt/Oracle/diag/rdbms/dbserver1/dbserver1/trace/dbserver1_j003_10544.trc:
ORA-20011: Approximate Newcastle failed: ORA-01476: divisor is equal to zero
Environment
ORACLE 11G R2
RedHat 5.3 FOR 64 BIT
Solution
The conclusion on the Internet is a BUG.
Bug No: 6040840
Filed 09-MAY-2007 Updated 10-MAY-2007
Product Oracle Server-Enterprise Edition Product Version 9.2.0.8
Platform. AIX5L Based Systems (64-bit) Platform. Version No Data
Database Version 9.2.0.8 Affects Platforms Generic
Severity Severe Loss of Service Status Duplicate Bug. To Filer
Base Bug 5645718 Fixed in Product Version No Data
Problem statement:
DBMS_STATS.GATHER_TABLE_STATS fails with ORA-1476.
WORKAROUND: ----------- n/a. related bugs: ------------- Bug #5645718.
However, my database version is 11 GB. It should not be this BUG.
Check log discovery:
* ** 06:00:16. 870
GATHER_STATS_JOB: GATHER_TABLE_STATS ('"MIS"', '"T_SALES_ORDER_ITEM "','""',...)
ORA-20011: Approximate Newcastle failed: ORA-01476: divisor is equal to zero
Check the T_SALES_ORDER_ITEM table and report the following error when selecting the table:
ORA-01476: divisor is equal to zero
View the table structure:
Create table T_SALES_ORDER_ITEM
(
Id number (18) not null,
......
PREPAY_RATE number generated always as (ROUND (TO_NUMBER (TO_CHAR ("PREPAYMONEY") * 100/("PRICE" * "QUANTITY"), 2 ))
......
Finally, select price, quantity from T_SALES_ORDER_ITEM finds that price has a value equal to 0 !!! The problem is not difficult to solve. It is essential to discover the problem.
Modify the PREPAY_RATE column and add the decode judgment function:
PREPAY_RATE number generated always as (DECODE ("PRICE", 100, ROUND (TO_NUMBER (TO_CHAR ("PREPAYMONEY") */("PRICE" * "QUANTITY "), 2 )))