1

I have three sites,one site contians the employees table, while the other sites have materialized view of employees table . This is how i created the materialized views on the other sites.

CREATE MATERIALIZED VIEW employeesMV
   REFRESH FAST
   FOR UPDATE
AS
   SELECT * FROM manager.employees@managerlink;

so i just want to know how to update the master table employees after i make changes such as (insert or update) on the materialized view. Thank you in advance.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Húsàm Shüjáàdiiñ
  • 43
  • 7

1 Answers1

1

By default, materialized view can't be updated. However, if you use FOR UPDATE clause, you can do it, but those changes aren't reflected in MV's source table. Moreover, as soon as you refresh the MV, changes you've made will be lost.

Advanced replication covers it, but Oracle says that it is deprecated in 12cR1.

There's a walkthrough on Vinayaga Consultancy's blog, Updatable Materialized View, based on Oracle 11.2 (source) and 10.2 (target database) so - have a look. It isn't that trivial at all.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57