Using Oracle ON COMMIT option on materialized over database link

Question

I have a question.

I have a table on database 1 called Distribution_id which will hold no more than 2000 records at a time, I have a materialized view called Mv_Distribution_id which is a copy Distribution_id table on database 2.

I want to do a quick refresh of the materialized view Mv_Distribution_id on database 2 when ever there is a change to the data in the database table Distribution_id on database 1.

If the materialized view Mv_Distribution_id on database 2 has been created with the "ON COMMIT" option will it see that the database table on database 1 as been changed and automatically fast refreshed the materialized view, or doesn't the "ON COMMIT" option work over a database link and i'd need to refresh it using a packaged procedure or database table trigger on Distribution_id table.

The reason I've created a materialized view is sometimes database 1 is not available to database 2 therefore creating a copy is a quick solution.

Thanks in advance.

Answer 1

As documentation says:

A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view.

Materialized views (especially) make sense over database links because operations over the link can be slow, so it is handy to have data here. So ... no problem with it, I'd say.