When I showed up in office couple weeks ago, a client called and complained that a materialized view refresh failed, this refresh has been working with no errors in years. The client confirmed that there was no code change in the materialized view or any underlying tables.Refreshing the materialized view manually also failed in couple second with the same error :
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 1
The errors first looked like it was due to data in underlying tables which can not be fit in the defined materialized view. As a test, I captured the select statement for the materialized view and populated it in a temporary table to see if any column data types in temporary table is different from the materialized view column data type.
Interesting enough, all columns in temporary table and materialized view were the same in terms of data type and the length.So it can not be data type overflow ?!!!
Tracing the refresh (10046) did not resolve anything.
Eventually found that the issue was not the materialized view by itself but it was due to overflow of dba_tab_modifications which keeps track of insert/update/delete for each object.
Since the materialized view is refreshed on regular basis, Oracle 's number which keeps track of changes were high for the materialized view and when the refresh was done they were overflowed. Metalink ID 882997.1 implicitly reported this issue.
Oracle released a one-off patch for this issue but in interim, gathering stats on the materialized view should reset the number in the dictionary and then refresh should run in success :
select inserts, deletes from sys.mon_mods_all$ where obj# = &object_id;
INSERTS DELETES
---------- ----------
4295948847 4283835810
exec dbms_stats.gather_table_stats('@owner','&mv_name',estimate_percent=>5,cascade=> false);
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
select inserts, deletes from sys.mon_mods_all$ where obj# = 189467;
select inserts, deletes from sys.mon_mods_all$ where obj# = &object_id;
no rows
For your info, this issue was reported in 11gR1 (11.1.0.7)
