Home » Server Options » Replication » Updating table connected to materialized view
Updating table connected to materialized view [message #10397] Tue, 20 January 2004 22:59 Go to next message
Cheeku
Messages: 17
Registered: November 2003
Junior Member
Hi...
I have a snapshot that is refreshes every day once. But due to some constraints we are not able to run the replication scripts. I am trying to manually insert a row into the table.But it gives me error ORA:1732 Data manipulation operation cannot be performed on this view.
I looked at USer_snapshots and found out that updatable property is set to 'NO', I tried setting it to 'YES' but it din't allow me to do that..The error was Virtual column not allowed here.
Any help wud be really appreciated.
Regards
Re: Updating table connected to materialized view [message #10474 is a reply to message #10397] Sun, 25 January 2004 23:01 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Materialized views are by default read-only. You cannot update them nor can you alter them to be updatable.

See the "Advanced Replication" manual for details on how to create Updatable Materialized Views. Here is an example from the manual:

CREATE MATERIALIZED VIEW hr.departments <b>FOR UPDATE</b> AS
  SELECT * FROM hr.departments@orc1.world;

The following statement creates a materialized view group:

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname => 'hr_repg',
      master => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS');
END;
/

The following statement adds the hr.departments
materialized view to the materialized view group,
making the materialized view updatable:

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'departments',
      type => 'SNAPSHOT',
      min_communication => TRUE);
END;
/


Best regards.

Frank
Previous Topic: Materialized View COMPILE_STATE = ERROR
Next Topic: Complex View: How to convert into Materialized view
Goto Forum:
  


Current Time: Thu Mar 28 10:07:12 CDT 2024