How to refresh Materialized View using DB link in Oracle

Question

I have 3 schemas in Oracle. There's a Materialized View in the 3rd schema which I need to refresh from the 1st schema.

Below is the elaboration of the requirement:

uv1 (1st schema) --> db link to nwdb2 (2nd schema) --> nwdb3 (3rd schema) --> emp_de_mv (MV)

I need to refresh the emp_de_mv from uv1.

I'm already executing a SELECTstatement on MV from uv1 as follows, which is working successfully:

SELECT * FROM nwdb3.emp_de_mv@nwdb2;

I tried refreshing the MV from uv1 as follows as suggested here.

EXEC DBMS_MVIEW.refresh('nwdb3.emp_de_mv@nwdb2', 'C');

But it's giving me following error:

Error starting at line : 25 in command -
EXEC DBMS_MVIEW.refresh('nwdb3.emp_de_mv@nwdb2', 'C')
Error report -
ORA-20000: ORA-00979: illegal reference to remote database
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
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

Can anyone help me with above requirement?

Please note, I won't be able to create new DB link in uv1 due to security reasons.

Answer 1

ORA-20000: ORA-00979: illegal reference to remote database

You get this error message because, as the documentation for refresh() states:

"These materialized views can be located in different schemas and have different master tables or master materialized views. However, all of the listed materialized views must be in your local database."

The solution is to execute the refresh on the remote database, by invoking DBMS_MVIEW across the database link:

EXEC DBMS_MVIEW.refresh@nwdb2('nwdb3.emp_de_mv', 'C'); 

---

how am I able to access nwdb3 from nwdb2 directly, without DB link & why can't I access nwdb2 directly from uv1, without DB link?

It's your environment so I don't - can't - know the architecture. You describe nwdb2 as a schema but also describe it as a database link. I'm guessing you have a database link called nwdb2 which connects to a schema also called nwdb2 that has privileges on another schema nwdb3 in the remote database server. On the remote server nwdb2 can reference objects in nwdb3 schema without a database link because those schemas are local to each other. But nwdb1 must use a database link because it is not local to either schema.