{"id":101,"date":"2015-05-06T09:14:19","date_gmt":"2015-05-06T09:14:19","guid":{"rendered":"https:\/\/dbadave.co.uk\/?p=101"},"modified":"2016-02-09T23:01:08","modified_gmt":"2016-02-09T23:01:08","slug":"upgrade-issue-there-are-materialized-view-refreshes-in-progress","status":"publish","type":"post","link":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/","title":{"rendered":"Upgrade Issue – There Are Materialized View Refreshes In Progress"},"content":{"rendered":"

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”.<\/p>\n

When I ran the script, I got a warning message saying:<\/p>\n

WARNING: –> There are materialized view refreshes in progress.<\/em>.. Ensure all materialized view refreshes are complete prior to upgrade.<\/em><\/p>\n

Checking Materialized View Refreshes In Progress<\/strong><\/em><\/h2>\n

Now, according to the 11g Release 2 Upgrade Guide<\/a>\u00a0you 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\u00a0materialized view refreshes are currently running. However, the query it says you should run\u00a0is this:<\/p>\n

SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;<\/em><\/p>\n

The Query’s Not Right!<\/strong><\/em><\/h2>\n

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:<\/p>\n

select s.obj#, o.obj#, s.containerobj#, lastrefreshdate, pflags, xpflags, o.name, o.owner#, bitand(s.mflags, 8)\u00a0from obj$ o, sum$ s\u00a0where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;<\/em><\/p>\n

Now when I ran the above command it returned two rows even though no materialized views where actually refreshing at the time.<\/p>\n

How To Fix The Issue<\/strong><\/em><\/h2>\n

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.<\/p>\n

Just for your reference in case you ever come across this issue, you can manually refresh materialized views using the dbms_mview database package:<\/p>\n

exec dbms_mview.refresh(‘<materialized_view_name>’);<\/em><\/p>\n

where <materialized_view_name><\/em> is the value returned in the o.name<\/em> column in the above query to check for current materialized view refreshes.<\/p>\n

I hope this short article proves useful to\u00a0you if you ever experience the same problem where Oracle reports materialized view refreshes in progress when doing an upgrade.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":379,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[4,12],"yoast_head":"\nMaterialized View Refreshes In Progress | DBADave<\/title>\n<meta name=\"description\" content=\"I recently hit a problem where Oracle reported materialized view refreshes in progress during an upgrade. Visit now to read the solution.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Materialized View Refreshes In Progress | DBADave\" \/>\n<meta property=\"og:description\" content=\"I recently hit a problem where Oracle reported materialized view refreshes in progress during an upgrade. Visit now to read the solution.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\" \/>\n<meta property=\"og:site_name\" content=\"DBA Dave - Oracle Training\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-06T09:14:19+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-02-09T23:01:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"770\" \/>\n\t<meta property=\"og:image:height\" content=\"340\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40\"},\"headline\":\"Upgrade Issue – There Are Materialized View Refreshes In Progress\",\"datePublished\":\"2015-05-06T09:14:19+00:00\",\"dateModified\":\"2016-02-09T23:01:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\"},\"wordCount\":382,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbadave.co.uk\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg\",\"keywords\":[\"Oracle Database\",\"Problems and Solutions\"],\"articleSection\":[\"Upgrades\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\",\"url\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\",\"name\":\"Materialized View Refreshes In Progress | DBADave\",\"isPartOf\":{\"@id\":\"https:\/\/dbadave.co.uk\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg\",\"datePublished\":\"2015-05-06T09:14:19+00:00\",\"dateModified\":\"2016-02-09T23:01:08+00:00\",\"description\":\"I recently hit a problem where Oracle reported materialized view refreshes in progress during an upgrade. Visit now to read the solution.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage\",\"url\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg\",\"contentUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg\",\"width\":770,\"height\":340,\"caption\":\"Materialized View Refreshes Are In Progress - Solution\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbadave.co.uk\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Upgrade Issue – There Are Materialized View Refreshes In Progress\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbadave.co.uk\/#website\",\"url\":\"https:\/\/dbadave.co.uk\/\",\"name\":\"DBA Dave - Oracle Training\",\"description\":\"Expert DBA Tips & Advice\",\"publisher\":{\"@id\":\"https:\/\/dbadave.co.uk\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbadave.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbadave.co.uk\/#organization\",\"name\":\"DBA Dave\",\"url\":\"https:\/\/dbadave.co.uk\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png\",\"contentUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png\",\"width\":170,\"height\":50,\"caption\":\"DBA Dave\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/dbadave.co.uk\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Materialized View Refreshes In Progress | DBADave","description":"I recently hit a problem where Oracle reported materialized view refreshes in progress during an upgrade. Visit now to read the solution.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/","og_locale":"en_US","og_type":"article","og_title":"Materialized View Refreshes In Progress | DBADave","og_description":"I recently hit a problem where Oracle reported materialized view refreshes in progress during an upgrade. Visit now to read the solution.","og_url":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/","og_site_name":"DBA Dave - Oracle Training","article_published_time":"2015-05-06T09:14:19+00:00","article_modified_time":"2016-02-09T23:01:08+00:00","og_image":[{"width":770,"height":340,"url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg","type":"image\/jpeg"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#article","isPartOf":{"@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/"},"author":{"name":"admin","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40"},"headline":"Upgrade Issue – There Are Materialized View Refreshes In Progress","datePublished":"2015-05-06T09:14:19+00:00","dateModified":"2016-02-09T23:01:08+00:00","mainEntityOfPage":{"@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/"},"wordCount":382,"commentCount":0,"publisher":{"@id":"https:\/\/dbadave.co.uk\/#organization"},"image":{"@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage"},"thumbnailUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg","keywords":["Oracle Database","Problems and Solutions"],"articleSection":["Upgrades"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/","url":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/","name":"Materialized View Refreshes In Progress | DBADave","isPartOf":{"@id":"https:\/\/dbadave.co.uk\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage"},"image":{"@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage"},"thumbnailUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg","datePublished":"2015-05-06T09:14:19+00:00","dateModified":"2016-02-09T23:01:08+00:00","description":"I recently hit a problem where Oracle reported materialized view refreshes in progress during an upgrade. Visit now to read the solution.","breadcrumb":{"@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#primaryimage","url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg","contentUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/05\/materialized-view-refreshes-are-in-progress-solution-1.jpg","width":770,"height":340,"caption":"Materialized View Refreshes Are In Progress - Solution"},{"@type":"BreadcrumbList","@id":"https:\/\/dbadave.co.uk\/upgrade-issue-there-are-materialized-view-refreshes-in-progress\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbadave.co.uk\/"},{"@type":"ListItem","position":2,"name":"Upgrade Issue – There Are Materialized View Refreshes In Progress"}]},{"@type":"WebSite","@id":"https:\/\/dbadave.co.uk\/#website","url":"https:\/\/dbadave.co.uk\/","name":"DBA Dave - Oracle Training","description":"Expert DBA Tips & Advice","publisher":{"@id":"https:\/\/dbadave.co.uk\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbadave.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbadave.co.uk\/#organization","name":"DBA Dave","url":"https:\/\/dbadave.co.uk\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/","url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png","contentUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png","width":170,"height":50,"caption":"DBA Dave"},"image":{"@id":"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/dbadave.co.uk\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/101"}],"collection":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/comments?post=101"}],"version-history":[{"count":12,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/101\/revisions"}],"predecessor-version":[{"id":380,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/101\/revisions\/380"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/media\/379"}],"wp:attachment":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/media?parent=101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/categories?post=101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/tags?post=101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}