How to catch errors when refresh nested materialized views

Question

Materialized View "MV_AMP":

CREATE MATERIALIZED VIEW MV_AMP
  NOLOGGING
  BUILD IMMEDIATE
  REFRESH FORCE
  ON DEMAND
AS
Select a, b, c from amp;

Materialized View "MV_BOT" that dependens on "MV_AMP":

CREATE MATERIALIZED VIEW MV_BOT
  NOLOGGING
  BUILD IMMEDIATE
  REFRESH FORCE
  ON DEMAND
AS
SELECT bot.x, bot.y, mv_amp 
FROM bot, mv_amp
WHERE bot.a = mv_amp.a;

And create a unique index in mv_bot:

CREATE UNIQUE INDEX mv_bot_idx001 ON mv_bot(x, a);

After views and index create successful, suppose I add a duplicate value that cause an error like (dup_val_on_index) when refresh the mv_bot due the unique index.

So I do a refresh in MV_AMP (the main view) using nested=TRUE, and Oracle didn't raise an error:

BEGIN
  dbms_mview.refresh_dependent(number_of_failures => n_failures,
                                   list => 'MV_AMP',
                                   atomic_refresh => TRUE,
                                   nested => TRUE);

EXCEPTION
  WHEN
    OTHERS THEN
        -- it never reach this code
        dbms_output.put_line('Errors: '||SQLERRM);
END;

n_failures returns: 0 and it never reaches the dbms_output inside the exception.

I need to catch the error when oracle tries to updates the nested MVs and log in a table.

Using Oracle 11g.

Marmite Bomber Marmite Bomber · Accepted Answer · 2019-04-02T14:00:17

The EXCEPTION block ist reached only in case there is a duplicated row in the materialized view MV_BOT - which is not the case.

You may ask why; the most probalble answer is that you need to refresh the materialized view MV_AMP as well to get the dup in the join of MV_BOT.

While reading the documentation of dbms_mview.refresh_dependent you realize, that you must start with the table AMP to get both MV's refreshed (starting with MV_AMP refreshes only the dependent MV MV_BOT)

Test Case

create table amp (
a number,
b number,
c number);

create table bot (
a number,
x number,
y number);

CREATE MATERIALIZED VIEW MV_AMP
  NOLOGGING
  BUILD IMMEDIATE
  REFRESH FORCE
  ON DEMAND
AS
Select a, b, c from amp;

CREATE MATERIALIZED VIEW MV_BOT
  NOLOGGING
  BUILD IMMEDIATE
  REFRESH FORCE
  ON DEMAND
AS
SELECT bot.x, bot.y, mv_amp.a 
FROM bot, mv_amp
WHERE bot.a = mv_amp.a;

CREATE UNIQUE INDEX mv_bot_idx001 ON mv_bot(x, a);

insert into amp(a,b,c) values(1,1,1);
insert into bot(a,x,y) values(1,1,1);
insert into bot(a,x,y) values(1,1,3);
commit;

DECLARE
  n_failures NUMBER;
BEGIN
  dbms_mview.refresh_dependent(number_of_failures => n_failures,
                                   list => 'AMP',
                                   atomic_refresh => TRUE,
                                   nested => TRUE);
   dbms_output.put_line('Failures: '||n_failures);                                                                
EXCEPTION
  WHEN
    OTHERS THEN
        dbms_output.put_line('Errors: '||SQLERRM);
END;
/

--> Errors: ORA-12008: error in materialized view refresh path
--> ORA-00001: unique constraint (xxxxx.MV_BOT_IDX001) violated

How to catch errors when refresh nested materialized views

1 Answers