{"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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[22],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-scripts-tools"],"acf":[],"jetpack-related-posts":[{"id":73,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/73\/","url_meta":{"origin":16,"position":0},"title":"EBS Clone Considerations &#8211; Database Refresh","author":"Biju","date":"June 15, 2013","format":false,"excerpt":"When doing database refresh for EBS databases, the following steps may save time on completing the clone faster... Copy the code relevant to your environment to a file and run the script file prior to the clone to save the configuration information. Once database refresh completed, you may restore the\u2026","rel":"","context":"In &quot;Oracle E-Business Suite&quot;","block_context":{"text":"Oracle E-Business Suite","link":"https:\/\/bijoos.com\/oraclenotes\/category\/ebs\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":222,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/222\/","url_meta":{"origin":16,"position":1},"title":"Validate Database Links &#8211; Script","author":"Biju","date":"October 29, 2013","format":false,"excerpt":"Here is a PL\/SQL script to validate the database links accessible to an user. \u00a0I wrote this to identify the working links after a database refresh or to verify links that connect from non-prod to prod... Hope it helps... \u00a0 REM Validate Database Links REM Private links under connected user\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1686,"url":"https:\/\/bijoos.com\/oraclenotes\/2016\/1686\/","url_meta":{"origin":16,"position":2},"title":"Oracle Daily Tidbits March 2016","author":"Biju","date":"April 1, 2016","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in March\u00a02016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and use...\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1480,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/1480\/","url_meta":{"origin":16,"position":3},"title":"Oracle Tidbits &#8211; December 2014","author":"Biju","date":"December 31, 2014","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published newly at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in December\u00a02014. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":211,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/211\/","url_meta":{"origin":16,"position":4},"title":"MOS Docs to Review for EBS DB Upgrade to 11gR2 (Latest Patch Release)","author":"Biju","date":"October 7, 2013","format":false,"excerpt":"If you are considering Oracle database upgrade from 9i\/10g to 11gR2 or applying release patches (11.2.0.1 to 11.2.0.3 or 11.2.0.4), several issues can be avoided by applying the recommended patches and PSU. \u00a0And if you are not on the latest 11gR2 release (11.2.0.3 or 11.2.0.4), I strongly recommend getting there,\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1777,"url":"https:\/\/bijoos.com\/oraclenotes\/2016\/1777\/","url_meta":{"origin":16,"position":5},"title":"Oracle Tidbits &#8211; October 2016 #oratidbit","author":"Biju","date":"November 3, 2016","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in October 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"Oracle TidBit","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2014\/05\/TidBit.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_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}]}}