Oracle: Create MVIEW using DBLINK with COMMIT COMPLETE option

I'm using MVIEW (Materialized Views) to select data from another database through DBLINK and set schedule to refresh daily.

The problem came when my user commented that they need the data from the source database faster. So, my team tried to set MVIEW with COMMIT COMPLETE option to get the data from source table immediately whenever the data is updated.
























I found that IT'S IMPOSSIBLE to use this option through DBLINK as Oracle doesn't have mechanism to let MVIEW knows that there is a change. Therefore, MVIEW will not activate to pull the data from the source.

The other solution that I might have to use is to set schedule to be hourly updating instead. At least, for now until I find the better way.

References:
http://hemantoracledba.blogspot.com/2008/06/mvs-with-refresh-on-commit-cannot-be.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1167235330355

Popular posts from this blog

Microsoft Word: Make picture background to be transparent

LINE: Change the contact name

Microsoft Visio: How to set default font