There are 3 types of Materialized Views: read-only, updatable and
writeable. Yours is writeable, not updatable. You can change data in a
writeable MV, but the data will not be pushed to the master table, and
the changes will be lost when the next refresh occurrs and the MV is
refreshed with data from the master table. To make an MV updatable, it
needs to be in a "Materialized View Group". Look up
DBMS_REPCAT.CREATE_MVIEW_REPGROUP, DBMS_REPCAT.CREATE_MVIEW_REPOBJECT
and ORACLE mannual on how to use them.
manikroy_bd_at_yahoo.com (MC) wrote in message news:<648c5dd4.0304192114.348e8ad4_at_posting.google.com>...
> Hello everyone,
>
> Could somebody help me on the following issue...
>
> ** Is updateable snapshot able to update master table?
>
> If yes then where is the problem that I do?
>
> Master database
> ---------------
> SQL>sqlplus SYS/sys123_at_testdb
> SQL>create user xyx identified by xyz;
> SQL>grant connect to xyz;
> SQL>grant resource to xyz;
> SQL>commit;
> SQL>exit
>
> SQL>sqlplus xyz/xyz_at_testdb
> SQL>create table z(a1 number primary key, a2 varchar2(50));
> SQL>insert into z(a1, a2) values(100, 'test1');
> SQL>commit;
> SQL>create snapshot log on z;
> SQL>exit
>
> Snapshot database site
> ----------------------
> SQL>sqlplus SYS/sys123_at_snapdb
> SQL>create user pq identified by pq;
> SQL>grant connect to pq;
> SQL>grant resource to pq;
> SQL>grant dba to pq; #Is it excessive privilege?
> SQL>commit;
> SQL>exit
>
> SQL>sqlplus pq/pq_at_snapdb
> SQL>create database link abc.replica connect to xyz identified by xyz
> using 'testdb';
> SQL>create snapshot zsup refresh start with sysdate next sysdate +
> 10/(24*60) with primary key for update as select * from z_at_abc.replica;
> SQL>insert into zsup(a1,a2) values(2000, 'New value');
> SQL>commit;
> SQL>select * from zsup;
> A1 A2
> -----------
> 100 test1
> 2000 New value
>
> SQL>exit
>
> ** But 10 minutes later, record (2000, 'New value') is vanished from
> the snapshot 'zsup' and it does not update the master table 'z'. Only
> 1 record is found on both master table and snapshot table.
>
> I want to update master table thru the snapshot. Is it possible? Is it
> a bad work? Any clue would be appreciated?
>
>
> MC
Received on Sun Apr 20 2003 - 12:03:24 CDT
