ORA-00947: not enough values when refreshing materialized views

This is typical scenario for customers using materialized views to replicate below error -


SQL> exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);
BEGIN dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

Base Table -

create table sample_tb(id number primary key,name varchar2(10),val number);

Insert data -

insert into sample_tb values(1,'ABC',99);

Materialized View -

create materialized view sample_tb_mv
as
select * from sample_tb;

Check data -

select * from sample_tb;

        ID NAME              VAL
---------- ---------- ----------
         1 ABC                99

Check metadata -

SQL> set lines 170
set pages 1200
set long 9999999
select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','SAMPLE_TB_MV') from dual;SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','SAMPLE_TB_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."SAMPLE_TB_MV" ("ID", "NAME", "VAL")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

Drop column from base-table 

alter table sample_tb drop column val;

Check data for materialized view again -

select * from sample_tb_mv;
        ID NAME              VAL
---------- ---------- ----------
         1 ABC                99

Try to manually refresh again-

exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);

SQL> exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);
BEGIN dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

SQL>

Check metadata (mv metadata is still the same)- 

set lines 170
set pages 1200
set long 9999999
select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','SAMPLE_TB_MV') from dual;
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','SAMPLE_TB_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."SAMPLE_TB_MV" ("ID", "NAME", "VAL")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

Solution -

Drop and recreate materialized view -

SQL> drop  materialized view sample_tb_mv;

Materialized view dropped.

SQL> create materialized view sample_tb_mv
as
select * from sample_tb;

Try to refresh and check metadata again -

SQL> exec dbms_mview.refresh('SAMPLE_TB_MV','c',ATOMIC_REFRESH => FALSE);

PL/SQL procedure successfully completed.


SQL> set lines 170
set pages 1200
set long 9999999
select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','SAMPLE_TB_MV') from dual;SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','SAMPLE_TB_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."SAMPLE_TB_MV" ("ID", "NAME")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
................

We can conclude that for ORA-00947: not enough values, on materialized views, we should follow below approach -

1. Check for metadata of both base table, materialized view log and materialized view.
2. Compare columns of base table and materialized view.


Before wrapping up, 2 clauses worth noticing for materialized views are -
a. preserve table-
This clause will be used with 'drop materialized view' command to make sure that materialized view is preserved as a stand alone table and this will save us some time when recreating materialized views.

b. on prebuilt table-
When recreating materialized view, we need to use this clause so that 'prebuilt table' that was preserved during 'delete materialized view' command is used.