CODEWITHSUNDEEP
materialized-viewsoracle19c
Kriti Gupta
Kriti Gupta

Reputation: 23

Unable to create materialized view on remote database with 'Refresh Fast On commit'

I have one table tab1 in database1 on which I have created materialized view log. Now my requirement is to create one materialized view in database2 with real time data such that as soon as the data is committed in my tab1, it should get reflected in my mview. For this I have tried to create mview in database2 with 'refresh fast on commit' option but it is giving the below error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Q1.) Is it not possible at all to create mview in remote database with on commit option? Q2.) If not then why? If yes, the how? Q3.) What would happen when db link is down?

Upvotes: 0

Views: 1211

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

  1. It is not possible to have an on commit materialized view that references remote objects. That's one of the documented restrictions.
  2. It would be very challenging from an implementation perspective to allow materialized views to do an on commit refresh while referencing remote objects. Materialized views generally pull data from the remote database. In this case, the commit process on the remote database would have to notify the local database that changes were being committed, which would likely require a new process on the local database to listen for these notifications and to initiate the refresh. That would also tend to make commits on the local database quite slow. And it would mean that you couldn't commit on the local database if the database link was down which would be a major hit to reliability. Synchronous replication of data between databases imposes large costs on performance and reliability that are very rarely worth it.
  3. You'd be unable to commit changes on the local database. So your local database would be functionally unusable if the remote database was down.

Upvotes: 3

Related Questions