I built a report for a customer 2 years ago that uses an overnight stored procedure to refresh the MVs associated with the report. This report is built off of very large scope of data/calculations, so there are multiple materialized views throughout the process, to help with data populating quicker. Since making a minor code change a month ago to just one view, it seems the overnight procedure refresh for the materialized views, isn't working properly, and am unsure why.
Stored Procedure that automatically runs overnight, every night:
create or replace PROCEDURE surge_refresh_procedure
IS
BEGIN
DBMS_MVIEW.REFRESH('SCHEMA.COMBINE1_MV', method => 'C');
DBMS_MVIEW.REFRESH('SCHEMA.SURGE_DET_MV', method => 'C');
DBMS_MVIEW.REFRESH('SCHEMA.SURGE_A0A_MV', method => 'C');
DBMS_MVIEW.REFRESH('SCHEMA.SURGE_REL_MV', method => 'C');
DBMS_MVIEW.REFRESH('SCHEMA.SURGE_MET_MV', method => 'C');
END;
The materialized views are a direct select * from their associated views, which should be returning the same amount of results. When I run the statement select distinct srt from surge_det_v where srt2_date like '2020%', I return 3 results. When I run select distinct srt from surge_det_mv where srt2_date like '2020%', I return 6 results.
Another additional tidbit. If I do a manual refresh of CALL DBMS_MVIEW.REFRESH('SCHEMA.SURGE_DET_MV', method => 'C'); once I begin work the next morning, then that select distinct srt from surge_det_mv where srt2_date like '2020%' will actually refresh as it should, and return the 3 results. But when the stored procedure runs the next night, we're back to square one.
There weren't any changes to the stored procedure itself. I've tried recreating the procedure, by chance that would have repicked whatever is 'wrong' up, and still nothing. I've tried also dropping and recreating the MV in question, but still nothing.
Any ideas on why the stored procedure would all of a sudden not be catching the data properly?
