Materialized view with DB Link error when REFRESH ON COMMIT

The below example is to show how to refresh a materialized view on a table across a DB Link. This situation can occur when you have the main application schema in one PDB and a reporting schema on another PDB and you want to refresh data in the reporting schema using materialized views. We can create the materialized view with a db link. The refresh methods are ON DEMAND and ON COMMIT. But in the case of REFRESH FAST ON COMMIT we get the below error.

SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

To work around this we can do the following 

1. Create the materialized view with REFRESH FAST ON DEMAND and give a refresh frequency. 

2. Create the materialized view without any REFRESH clause and then use the DBMS_MVIEW.REFRESH package to manually refresh the mview or through a PLSQL scheduler. The drawback in this case is that if there are a large no of tables to be refreshed, we will need to create that many schedulers or in a single scheduler, refresh all the miviews. This will create a performance issue as even for tables for which there is no change a refresh will be triggered. 

3. We can user Oracle Change notification to trigger a refresh every time a insert, update or delete event is committed on the table. I will show each of these methods of refresh in the examples below. We first create the DB Link from SYTEM to HR schema as follows

--DROP DATABASE LINK SYSTEM_TO_HR;
CREATE DATABASE LINK SYSTEM_TO_HR 
CONNECT TO HR IDENTIFIED BY HR
USING '(DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
         )
         (CONNECT_DATA =
             (SERVICE_NAME = XEPDB1)
        )
     )';

You can test it as follows

SELECT * FROM EMPLOYEES@SYSTEM_TO_HR;

On the main HR schema run the following

SQL> CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;
SQL> GRANT SELECT ON EMPLOYEES TO SYSTEM;

Create a materialized view in Example 1

SQL> DROP MATERIALIZED VIEW MV_EMPLOYEES;

SQL> CREATE MATERIALIZED VIEW MV_EMPLOYEES
     AS SELECT * FROM HR.EMPLOYEES;

materialized view MV_EMPLOYEES created.

exec dbms_mview.refresh('MV_EMPLOYEES');

SELECT * FROM  MV_EMPLOYEES WHERE EMPLOYEE_ID = 100;

Example 2
SQL>  DROP MATERIALIZED VIEW MV_EMPLOYEES;
SQL>  CREATE MATERIALIZED VIEW MV_EMPLOYEES
      REFRESH FAST ON DEMAND
      START WITH SYSDATE
      NEXT (SYSDATE + 1/(24 * 60 * 5))
      AS SELECT * FROM EMPLOYEES@SYSTEM_TO_HR;
materialized view MV_EMPLOYEES created.

SELECT * FROM  MV_EMPLOYEES WHERE EMPLOYEE_ID = 100;

Refresh FAST ON COMMIT gives error as below

DROP MATERIALIZED VIEW MV_EMPLOYEES;
 CREATE MATERIALIZED VIEW MV_EMPLOYEES
 REFRESH FAST ON COMMIT
 AS SELECT * FROM EMPLOYEES@SYSTEM_TO_HR;
 
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

3. Using the Oracle DB Change Notification

To Find List of Database objects using a Database link

DECLARE

  TYPE objCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
  cur_dblink   objCurTyp;  
  CURSOR C1
  IS
  SELECT OWNER, DB_LINK FROM DBA_DB_LINKS;
  
  v_sql  VARCHAR2(1000);
  v_object_name  VARCHAR2(100);
  
BEGIN
  FOR rec in C1
  LOOP --{
    v_sql := 'select DISTINCT(name) from dba_source where upper(text) like ''%' || rec.DB_LINK || '%'' AND OWNER = ' || rec.OWNER;
--    dbms_output.put_line('v_sql ' || v_sql);
--     OPEN cur_dblink FOR 'select DISTINCT(name) from dba_source where upper(text) like ''%' || rec.DB_LINK || '%'' AND OWNER = ' || rec.OWNER;
     OPEN cur_dblink FOR 'select DISTINCT(name) from dba_source where upper(text) like ''%' || rec.DB_LINK || '%'' AND OWNER = :b' USING  rec.OWNER;
     LOOP --{
      FETCH cur_dblink INTO v_object_name;
      EXIT WHEN cur_dblink%NOTFOUND;
      dbms_output.put_line('v_owner ' || rec.OWNER || ' db_link ' || rec.DB_LINK || ' v_object_name ' || v_object_name);
      
     END LOOP; --}
     CLOSE cur_dblink;
  END LOOP; --}

END;

Thought for the day

My son, if you have put up security for your neighbor, 

if you have shaken hands in pledge for a stranger, 

you have been trapped by what you said, ensnared by the words of your mouth. 

So do this, my son, to free yourself, since you have fallen into your neighbor’s hands:

Go—to the point of exhaustion—[a] and give your neighbor no rest! 

Allow no sleep to your eyes, no slumber to your eyelids.

Free yourself, like a gazelle from the hand of the hunter, like a bird from the snare of the fowler.

Proverbs 6:1-5