数据库报错
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 ndv failed: ora-01476: divisor is equal to zero
环境
oracle 11g r2
redhat 5.3 for 64 bit
解决
网上给出的结论是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.
不过我的数据库版本是11g，，应该不是这个bug。
检查日志发现：
*** 2012-09-29 06:00:16.870
gather_stats_job: gather_table_stats('mis','t_sales_order_item','', ...)
ora-20011: approximate ndv failed: ora-01476: divisor is equal to zero
检查t_sales_order_item表发现该表select的时候也报错：
ora-01476: divisor is equal to zero
查看表结构：
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))
......
最后 select price,quantity from t_sales_order_item发现price有等于0的值！！！问题并不难解决，发现问题才是至关重要的。
修改prepay_rate列，添加decode判断函数：
prepay_rate number generated always as (decode(price,0,0,round(to_number(to_char(prepaymoney))*100/(price*quantity),2)))