I was doing an Oracle database 11g release 2 upgrade for a client at the weekend. As part of this process there is a pre-upgrade script you have to run called “utlu112i.sql”.
When I ran the script, I got a warning message saying:
WARNING: –> There are materialized view refreshes in progress... Ensure all materialized view refreshes are complete prior to upgrade.
Checking Materialized View Refreshes In Progress
Now, according to the 11g Release 2 Upgrade Guide you can’t upgrade until all materialized views have finished refreshing (which makes sense really). It then gives a query to run to see if any materialized view refreshes are currently running. However, the query it says you should run is this:
SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;
The Query’s Not Right!
It turns out that this query is wrong. Well not so much wrong as incomplete and doesn’t give you all the information. The actual query you need to run is:
select s.obj#, o.obj#, s.containerobj#, lastrefreshdate, pflags, xpflags, o.name, o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
Now when I ran the above command it returned two rows even though no materialized views where actually refreshing at the time.
How To Fix The Issue
So what I did was manually refresh the materialized views it reported where refreshing, just to try and bring everything in sync and then ran the query again. This time it returned no rows and I was able to continue with the upgrade, the rest of which went smoothly thankfully.
Just for your reference in case you ever come across this issue, you can manually refresh materialized views using the dbms_mview database package:
exec dbms_mview.refresh(‘<materialized_view_name>’);
where <materialized_view_name> is the value returned in the o.name column in the above query to check for current materialized view refreshes.
I hope this short article proves useful to you if you ever experience the same problem where Oracle reports materialized view refreshes in progress when doing an upgrade.