Skip to Main Content
  • Questions
  • Refresh Materialized view when there is records in the source table


Question and Answer

Chris Saxon

Thanks for the question, Santhosh.

Asked: June 21, 2016 - 9:07 am UTC

Answered by: Chris Saxon - Last updated: June 29, 2016 - 3:28 am UTC

Category: Developer - Version: 9.0.4

Viewed 1000+ times

You Asked

Hi ,

Need help to refresh one materialize view only when there is records in the table.

say example .

create materialize view MABCD as
select field1,field2,field3 from abcdef ;

my requirement is if abcdef have records greater than zero then the materialized view MABCD should be refreshed, if the table abcdef have zero records then materialized view MABCD should not be refreshed and should contain old data.

Can you kindly help on this.

Many Thanks

and we said...

There isn't an option to "conditionally" refresh a materialized view. Either you do it on commit, on demand (i.e. call refresh manually) or on a schedule.

If you want an MV to only refresh after inserts, then you should place a call to:


after your inserts, but not your deletes.

This requirement seems bizarre though. Why do you need to do this?

and you rated our response

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Review



June 21, 2016 - 1:04 pm UTC

Reviewer: Ghassan

Create a job or what else plsql sub-programs ..
Get the count
..If count ... then dbms redress. . End if;

Sometimes the simplest things didn't go up to mind.
Chris Saxon


June 21, 2016 - 2:00 pm UTC

I'm not following you. You mean have a job in the background that periodically checks and refreshes the MV if required?

You could do this. It's not clear what the requirement is though. If it's "simulate refresh on commit, but only for inserts" then the job approach will likely miss some data.

If it's "refresh whenever, but only refresh when there's data in the table", then yes, the job does the trick.


June 21, 2016 - 2:57 pm UTC

Reviewer: Ghassan

Yes seem that the requirements above fit with in the very last phrase.
But indeed no clear demand no 100 % adequate response .

June 28, 2016 - 12:09 pm UTC

Reviewer: Santhosh Kumar from india

Thanks for the reply ,

i have written small code to refresh based on condition, but im facing difficulty as im getting errors.

Below is the code which i written and calling it to refresh based on condition

a number(10) := 0;
-- check the boolean condition using if statement

IF( a > 0 ) THEN




Can you kindly help to know what is the issue in the above piece of code.

Chris Saxon


June 29, 2016 - 3:28 am UTC

When you say "i'm getting errors" and then dont tell us what the errors are, this is like us saying:

"I've got a favourite colour. Please tell me what it is"

You need to help us to help you.

More to Explore


More on PL/SQL routine DBMS_MVIEW here