Hi All,
I create a materialized view (complex type), the tables come from another DB so, the view is created via DB link. During refresh I got ora-12008 error. The interesting thing is, even I set ATOMIC_REFRESH to FALSE. So, it shouldn't be related to the ora-1555 then. After I google it, I found the following information. However, I can't understand what causes the problem or is it bug?
Note that I use DBMS_SNAPSHOT.REFRESH packge in order to refresh the MV.
Unable to refresh materialized view
ORA-12008: error in materialized view refresh path... Bug?
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:409663900346414175
ORA-12008 tips
In the one of the above link it is written the following
Grant SELECT privilege on the master table directly to the materialized view
owner.
Privileges gained through ROLES are not sufficient in PL/SQL. That is why they
cannot be used when running DBMS_SNAPSHOT package.
The whole tables have been granted to the MV owner. Even, the synonyms are created like follow.
CREATE SYNONYM emp_table FOR hr.employees@remotedb;
select * from v$version;
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Thanks