{"id":16,"date":"2011-10-14T01:01:00","date_gmt":"2011-10-14T01:01:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=16"},"modified":"2026-02-07T15:32:37","modified_gmt":"2026-02-07T21:32:37","slug":"script-to-refresh-materialized-views","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/16\/","title":{"rendered":"Script to Refresh Materialized Views"},"content":{"rendered":"<div dir=\"ltr\">The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast\u00a0refresh-able\u00a0materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month&#8230;<\/p>\n<div><\/div>\n<div>\n<pre class=\"lang:default decode:true \">REM Uncomment below line if EBS database\nREM alter session set current_schema=apps;\n\nset serveroutput on size 9999\n\nDeclare\ncursor mv2ref is select owner ||'.'|| \u00a0mview_name mview\n\u00a0 \u00a0 \u00a0 \u00a0from dba_mviews\n\u00a0 \u00a0 \u00a0 \u00a0where last_refresh_date between sysdate -30 and sysdate -1\n\u00a0 \u00a0 \u00a0 \u00a0and fast_refreshable = 'DML';\ndummy pls_integer;\n\nbegin\n\nREM Uncomment below line if EBS database\nREM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;\n\nfor rmv2ref in mv2ref loop\nbegin\n\u00a0 \u00a0 dbms_mview.refresh(rmv2ref.mview, 'F');\n\u00a0 \u00a0 dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));\n\nexception\nwhen others then\n\u00a0 \u00a0 dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));\n\u00a0 \u00a0 dbms_output.put_line(' \u00a0 \u00a0Error: '|| sqlcode ||':'||sqlerrm);\n\nend;\n\nend loop;\n\nend;\n\/<\/pre>\n<p><span style=\"font-size: 1rem; line-height: 1;\">Enjoy!<\/span><\/p>\n<\/div>\n<div><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast\u00a0refresh-able\u00a0materialized view that has not been refreshed in the past 24 hours, but was refreshed in &#8230; <a title=\"Script to Refresh Materialized Views\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2011\/16\/\" aria-label=\"Read more about Script to Refresh Materialized Views\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[22],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-scripts-tools"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":3007,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/16\/revisions\/3007"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}