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_mvas
select * from sample_tb;
Check data -
select * from sample_tb;
ID NAME VAL---------- ---------- ----------
1 ABC 99
Check metadata -
SQL> set lines 170set 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 170set 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_mvas
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.