Materialized View using a dblink from readonly standby database

readlink - This is a dblink created to read only standby database.


CREATE MATERIALIZED VIEW test1
   REFRESH COMPLETE
   AS SELECT S1.*
      FROM test@readlink S1;

ORA-16000: database open for read-only access

Although we think this materialized view is on a different database and dblink has just read access on remote readonly standby database, The logic here is different.

Whenever possible Oracle tries to register remote materialized view , This is helpful for fast refreshes. So here as its a readonly standby database it does not allow any writes about remote materialized views.


To overcome this issue - we can create the materialized view as below


CREATE MATERIALIZED VIEW test1
   REFRESH COMPLETE
   AS SELECT S1.*
      FROM test@readlink S1 
where rownum > 0;

The addition of rownum > 0 does not change the result set but allows it to make materialized view complex. This enables not to consider fast refresh and no registration at remote site.