Home » SQL & PL/SQL » SQL & PL/SQL » Error in materialized view refresh path (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Error in materialized view refresh path [message #675421] Thu, 28 March 2019 07:26 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

When we are executing refresh on Materialized view we are getting the following exception .


Error Msg :ORA-12008: error in materialized view refresh path
ORA-00947: not enough values,Line Number:ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at "QFXMAIN.SP_MV_REFRESH", line 68
"


We are going for the Complete refresh with the following code :


 dbms_mview.refresh(v_mv_name,'C',ATOMIC_REFRESH => FALSE)


Please help me to understand on this not enough values error with respect to the MV refresh

Thanks
SaiPradyumn
Re: Error in materialized view refresh path [message #675423 is a reply to message #675421] Thu, 28 March 2019 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the actual value in "v_mv_name"?
Try to directly do it with this value.
Have a look at alert.log.

Re: Error in materialized view refresh path [message #675424 is a reply to message #675421] Thu, 28 March 2019 11:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL> create table tbl(id number,name varchar2(10),val number);

Table created.

SQL> create materialized view tbl_mv
  2  as
  3  select * from tbl
  4  /

Materialized view created.

SQL> exec dbms_mview.refresh('TBL_MV','C',ATOMIC_REFRESH => FALSE)

PL/SQL procedure successfully completed.

SQL> alter table tbl drop column val;

Table altered.

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

*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 1


SQL> 

SY.
Re: Error in materialized view refresh path [message #675425 is a reply to message #675424] Thu, 28 March 2019 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good! I get the exact same lines than OP with your test case and his version (11.2.0.4):
SQL> create table tbl(id number,name varchar2(10),val number);

Table created.

SQL> create materialized view tbl_mv
  2  as select * from tbl
  3  /

Materialized view created.

SQL> exec dbms_mview.refresh('TBL_MV','C',ATOMIC_REFRESH => FALSE)

PL/SQL procedure successfully completed.

SQL> alter table tbl drop column val;

Table altered.

SQL> exec dbms_mview.refresh('TBL_MV','C',ATOMIC_REFRESH => FALSE)
BEGIN dbms_mview.refresh('TBL_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 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1
Re: Error in materialized view refresh path [message #675426 is a reply to message #675425] Thu, 28 March 2019 14:06 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
Same error in 12.2.0.1.

The DBA_DEPENDENCIES row exist but the code in the refresh is apparently buggy.

DEV1> ;
  1  select * from dba_dependencies
  2* where name = 'TBL' or referenced_name = 'TBL'
DEV1> /

OWNER                NAME       TYPE                REFERENCED_OWNER
-------------------- ---------- ------------------- --------------------
REFERENCED REFERENCED_TYPE     REFERENCED DEPE
---------- ------------------- ---------- ----
BOILEAU_JEANP TBL_MV           MATERIALIZED VIEW   BOILEAU_JEANP
TBL        TABLE                          REF


DEV1>

JP
Re: Error in materialized view refresh path [message #675427 is a reply to message #675426] Thu, 28 March 2019 15:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It no more exists after the attempt to refresh the view in 11.2:
SQL> create table tbl(id number,name varchar2(10),val number);

Table created.

SQL> create materialized view tbl_mv
  2  as select * from tbl
  3  /

Materialized view created.

SQL> alter table tbl drop column val;

Table altered.

SQL> select * from dba_dependencies where name in ('TBL','TBL_MV') or referenced_name in ('TBL','TBL_MV');
OWNER                          NAME                           TYPE               REFERENCED_OWNER
------------------------------ ------------------------------ ------------------ ------------------------------
REFERENCED_NAME                                                  REFERENCED_TYPE
---------------------------------------------------------------- ------------------
REFERENCED_LINK_NAME
------------------------------------------------------------------------------------------------------------------------
DEPE
----
MICHEL                         TBL_MV                         MATERIALIZED VIEW  MICHEL
TBL                                                              TABLE

REF
MICHEL                         TBL_MV                         MATERIALIZED VIEW  MICHEL
TBL_MV                                                           TABLE

REF

2 rows selected.

SQL> exec dbms_mview.refresh('TBL_MV','C',ATOMIC_REFRESH => FALSE)
BEGIN dbms_mview.refresh('TBL_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 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1


SQL> select * from dba_dependencies where name in ('TBL','TBL_MV') or referenced_name in ('TBL','TBL_MV');

no rows selected

[Updated on: Thu, 28 March 2019 15:23]

Report message to a moderator

Re: Error in materialized view refresh path [message #675435 is a reply to message #675427] Fri, 29 March 2019 02:47 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Hi


Thanks for replicating the issue.But in my case we are not doing any alternations to the base tables of materialized views .
Moreover i am able to see all dependencies with respect to the MV in DBA_DEPENDENCIES table

Is there any other scenario which can leads to this issue ?

Thanks
Saipradyumn
Re: Error in materialized view refresh path [message #675436 is a reply to message #675435] Fri, 29 March 2019 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post what you do and get?
Can you post a test case we can reproduce?

[Updated on: Fri, 29 March 2019 05:32]

Report message to a moderator

Re: Error in materialized view refresh path [message #675437 is a reply to message #675436] Fri, 29 March 2019 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you run the materialized view select on it's own does it work?
Re: Error in materialized view refresh path [message #675443 is a reply to message #675437] Fri, 29 March 2019 06:42 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Materialized view is working fine.When I pass name of Materialized view to refresh method ts giving the following error:




exec dbms_mview.refresh('MV_XXXX_REC','C',ATOMIC_REFRESH => FALSE)
Error report -
ORA-12008: error in materialized view refresh path
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.





Count of the following query is 6


 
select * from dba_dependencies where name = 'MV_XXXX_REC'  and  TYPE='MATERIALIZED VIEW';
Re: Error in materialized view refresh path [message #675444 is a reply to message #675443] Fri, 29 March 2019 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What about executing what is said in "Action" part and post the result, the REAL result not the faked output you gave?

Re: Error in materialized view refresh path [message #675445 is a reply to message #675444] Fri, 29 March 2019 07:30 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


H
Attached the screen shot for reference to get that issue
  • Attachment: Capture.PNG
    (Size: 45.04KB, Downloaded 1043 times)
Re: Error in materialized view refresh path [message #675446 is a reply to message #675445] Fri, 29 March 2019 10:11 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Did you try to create the MVIEW with the appropriate column list instead of a wildcard? I would suspect that it's getting confused.
Re: Error in materialized view refresh path [message #675447 is a reply to message #675443] Fri, 29 March 2019 10:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
saipradyumn wrote on Fri, 29 March 2019 07:42
Materialized view is working fine.When I pass name of Materialized view to refresh method ts giving the following error:
Obviously it is working fine. MV is created and has data as it was at last refresh or MV create time. Then some column was dropped in base table. That doesn't affect MV until you try to refresh it. And since MV definition has that dropped column you get not enough values:

SQL> create table tbl(id number,name varchar2(10),val number);

Table created.

SQL> insert into tbl values(1,'ABC',99);

1 row created.

SQL> create materialized view tbl_mv
  2  as
  3  select * from tbl
  4  /

Materialized view created.

SQL> select * from tbl_mv;

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

SQL> alter table tbl drop column val;

Table altered.

SQL> select * from tbl_mv;

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

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

*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-00947: not enough values
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 1


SQL> desc tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)

SQL> desc tbl_mv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)
 VAL                                                NUMBER

SQL> 

As you can see dropping column in base table has no affect on MV. It still has it. That's why we get not enough values.

SY.
Re: Error in materialized view refresh path [message #675460 is a reply to message #675447] Mon, 01 April 2019 04:38 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks one and all.
Re: Error in materialized view refresh path [message #675461 is a reply to message #675460] Mon, 01 April 2019 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So it is a dropped column?
When I said "If you run the materialized view select on it's own does it work?"
I didn't mean do:
select * from materialized_view

I meant run the select statement that was used to create the materialized view. It would have thrown an invalid identifier error and made the problem obvious.
Re: Error in materialized view refresh path [message #675469 is a reply to message #675461] Mon, 01 April 2019 10:04 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All,

Based on all of your inputs, I compared MV base query.Number of the columns in the declaration part & number of the columns in the select query are not equal.I have one extra column in the declaration where as there is corresponding column in select query.


Thanks for your valuable suggestions

Thnaks
SaiPradyumn




Previous Topic: how to replace date column
Next Topic: How to send sms to no of mobile numbers using url in plsql procedure
Goto Forum:
  


Current Time: Thu Mar 28 11:21:14 CDT 2024