updateable snapshot

From: MC <manikroy_bd_at_yahoo.com>
Date: 19 Apr 2003 22:14:24 -0700
Message-ID: <648c5dd4.0304192114.348e8ad4@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 - 00:14:24 CDT