You can also use off-line read only snapshots ..!
This example will help you ..
- *****************************************************************
- This sample refers to two nodes, the master node V7N1 and the
- snapshot node V7N2. Also, the demonstrated methodolgy does not
- make use of any Oracle Replication Option packages
--
- IMPORTANT: The order of the steps below is crucial to success
- *****************************************************************
- *****************************************************************
- Prepare the temporary schema on the master site
- *****************************************************************
- Create the snapshot owner
connect system/manager_at_2:v7n1
drop user temp cascade;
create user temp identified by temp default tablespace usr;
grant connect, resource, create snapshot to temp;
- SCOTT will maintain the snapshot log since TEMP
- will eventually be deleted
connect scott/tiger_at_2:v7n1
grant select on dept to temp;
drop snapshot log on scott.dept;
create snapshot log on scott.dept;
- TEMP is used to create the snapshot in order to facilitate
- the import and to simplify post export cleanup.
connect temp/temp_at_2:v7n1
drop snapshot dept;
create snapshot dept as select * from scott.dept;
- *****************************************************************
- Export ONLY the SNAP$_DEPT table from the temp schema
- *****************************************************************
host exp73 temp/temp_at_2:v7n1 file=d:\expdat.dmp tables=snap$_dept
- *****************************************************************
- Prepare the destination schema on the snapshot site
- *****************************************************************
- Create the snapshot owner
connect system/manager_at_2:v7n2
drop user temp cascade;
create user temp identified by temp default tablespace usr;
grant connect, resource, dba, create snapshot to temp;
- Precreate the snapshot DEPT under TEMP using an additinal 'WHERE 1=0'
- condition to ensure that no data is transmitted at snapshot create
- time, to avoid the import complications and to allow for the use of
- the fast refresh option post-import
connect temp/temp_at_2:v7n2
drop database link v7n1;
create database link v7n1
connect to scott identified by tiger using '2:v7n1';
drop snapshot dept;
create snapshot dept as select * from scott.dept_at_v7n1 where 1=0;
- **********************************************************************
- Import the SNAP$_DEPT table from the temp schema
- **********************************************************************
host imp73 temp/temp_at_2:v7n2 file=d:\expdat.dmp ignore=y tables=snap$_dept
- *****************************************************************
- Modify the mview$_dept definition on the snapshot site
- *****************************************************************
- Modify the MVIEW$_<table> view to remove the 'WHERE 1=0'
- condition from the WHERE clause
connect temp/temp_at_2:v7n2
select text from user_views where view_name = 'MVIEW$_DEPT';
create or replace view mview$_dept as
select "DEPTNO","DNAME","LOC", rowid m_row$$ from scott.dept_at_v7n1;
- Note: The temp (or snapshot) user must be granted dba for this
- next update to succeed!
select query_txt from sys.snap$ where mview = 'MVIEW$_DEPT';
update sys.snap$ set query_txt = 'select * from scott.dept_at_v7n1'
where mview = 'MVIEW$_DEPT';
- *****************************************************************
- Test the snapshot's fast refresh capabilities
- *****************************************************************
execute dbms_snapshot.refresh('dept','f');
select * from dept;
- *****************************************************************
- Drop the temporary user on the master site
- *****************************************************************
connect system/manager_at_2:v7n1
drop user temp cascade;
mariusdb_at_my-dejanews.com wrote in message
<70jp1s$hn7$1_at_nnrp1.dejanews.com>...
>A client of mine are running Oracle 7.3.3 on Sun Solaris They would like to
>keep an duplicate up-to-date copy of a subset of the data from a production
>database. The copy will run on a separate machine. They would like the copy
>to be kept up-to-date (real time) with data updates made to the original
>database (but not the other way around)
>
>How would I go about doing something like this??
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
begin 666 Yassir Khogaly.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S<VER#0I&
M3CI987-S:7(@2VAO9V%L>0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1%
M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE#
M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I,
M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP
M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E<G9E+F-O
M+G5K#0I54DPZ:'1T<#HO+W=W=RYA965U+F]R9RYU:PT*14U!24P[4%)%1CM)
M3E1%4DY%5#IY87-S:7) :VAO9V%L>2YF<F5E<V5R=F4N8V\N=6L-"E)%5CHQ
<.3DX,3 S,50R,#(U-3!:#0I%3D0Z5D-!4D0-"@``
`
end
Received on Sat Oct 31 1998 - 14:25:50 CST
