Materialized View big and empty after full refresh
Background:
Database 10.2.0.4 running on Windows 2003 Server R2 SP2.
Materialized view MV_A, selects from 3 base tables, 2 of which are partitioned, fast refresh on demand, approx 33 million rows. Initially populated with a complete refresh of 31 million rows about 2 months ago, and fast refreshed every 10 seconds since then.
Problem: Performed a partition split on one of the base tables and received this error on the next fast refresh:
ORA-32313: REFRESH FAST of "CUR"."MV_A" unsupported after PMOPs
Docs say a complete refresh is required.
So, created a new copy of the materialized view "MV_B", same storage params, same mview SELECT, same schema.
Did a complete refresh, the extents grew to the expected 10GB combined size. The DBMS_MVIEW.REFRESH reported 'PL/SQL procedure successfully completed' but the MVIEW is empty.
23:01:26 SQL> exec DBMS_MVIEW.REFRESH('MV_B','C','',FALSE,FALSE,0,0,0,TRUE) /* Complete */;
PL/SQL procedure successfully completed.
07:56:46 SQL> commit;
Commit complete.
08:17:51 SQL> select count(*) from mv_b;
COUNT(*)
----------
0
08:19:39 SQL>
Since the MVIEW table MV_B added 10 GB worth of extents during the refresh, it appears that the rows were inserted but then for some reason were rolled back. Nothing unusual in the alert.log. If I execute the MVIEW select statement by itself in SQLPlus, yes, I do get rows back.
The tablespaces housing the MVIEW table as well as the temporary and undo tablespaces are set to autoextend/unlimited to make sure they are not an issue.
Next I dropped and recreated the materialized view MV_B, identical except an added condition to the WHERE clause so that the number of rows returned is only 7 million instead of 33 million and that works fine - a complete refresh populates it and subsequent fast refreshes work correctly.
I'm stumped. Any ideas? Could we be running out of some memory resource that when exhausted, is not trapped properly by Oracle as an error?
- Tom