No question at this time
DBA Top 10
1 B. Vroman 13400
2 M. Cadot 10400
3 J. Schnackenberg 8300
4 T. Boles 7850
5 A. Kavsek 5700
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
8 P. Wisse 1000
10 . Lauri 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48302
Total active users1583
Act. users last 24h3
Act. users last hour0
Registered user hits last week198
Registered user hits last month812
Go up

Unable to refresh mv with out_of_place option
Next thread: RAC to NON RAC standby without asm
Prev thread: SQL*Developer rerurns a Java error

Message Score Author Date
Hi, I am working with Oracle RDBMS 12cR1. I ...... Lauri Dec 03, 2018, 16:20
Hi Lauri, Thanks for pointing this out. The ...... Philip Wisse Dec 05, 2018, 10:46
Hi Philip, Thanks for the links. I had reviewe...... Lauri Dec 05, 2018, 16:10

Follow up by mail Click here


Subject: Unable to refresh mv with out_of_place option
Author: Lauri, Netherlands
Date: Dec 03, 2018, 16:20, 12 days ago
Os info: Linux
Oracle info: 12cR1
Error info: ORA-12008, ORA-00900, ORA-06512
Message: Hi,

I am working with Oracle RDBMS 12cR1.

I have the following piece of code to refresh the materialized view:

alter session set recyclebin = off;
alter materialized view MV_OB_CUSTOMER_HIERARCHY compile;
begin
dbms_mview.refresh(
list => 'MV_OB_CUSTOMER_HIERARCHY',
method => 'C',
atomic_refresh => false,
out_of_place => true
);
end;
/

I end up with the following error:

Error report -
ORA-12008: error in materialized view refresh path
ORA-00900: invalid SQL statement
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 2
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.

Yet, this error does not appear all the time if I run that same piece of code.
Did someone faced this error before?

Thanks by advance for any tips

Kind Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Unable to refresh mv with out_of_place option
Author: Philip Wisse, Netherlands
Date: Dec 05, 2018, 10:46, 11 days ago
Message: Hi Lauri,

Thanks for pointing this out.

The out_of_place option in the docs:
https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG-GUID-51191C38-D52F-4A4D-B6FF-E631965AD69A

It may be fast but it has disadvantages:
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2010976_1.html

Crossref:
https://community.oracle.com/thread/4188138
---
Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Unable to refresh mv with out_of_place option
Author: Lauri, Netherlands
Date: Dec 05, 2018, 16:10, 10 days ago
Message: Hi Philip,

Thanks for the links.
I had reviewed the "Restrictions and Considerations with Out-of-Place Refresh".
The materialized view does have any of these restrictions. Yet it uses distinct, union all and full outer join...

Still searching for that error.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here