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  |
 |
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 #675424 is a reply to message #675421] |
Thu, 28 March 2019 11:13  |
Solomon Yakobson
Messages: 3305
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 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #675427 is a reply to message #675426] |
Thu, 28 March 2019 15:23 |
|
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #675443 is a reply to message #675437] |
Fri, 29 March 2019 06:42 |
|
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 #675447 is a reply to message #675443] |
Fri, 29 March 2019 10:48 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
saipradyumn wrote on Fri, 29 March 2019 07:42Materialized 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 #675461 is a reply to message #675460] |
Mon, 01 April 2019 04:47 |
cookiemonster
Messages: 13967
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Sat May 17 08:23:07 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
