Re: updateable snapshot

Hi,

In order to use updatable snapshots, you have to setup advanced replication features.

Once done, to create an updatable snaphot :

create snapshot snap_name
refresh fast
for update
as select ...

DML on this snaphot will be propaged to the master site.

Dias

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 - 03:58:27 CDT