Cloning Subset of PROD data to DEV

Oracle 9.2.0.6 EE on HP-UX 11.11

I'm putting together a solution to copy the contents of 30+ tables from our PROD database to our DEV database. My strategy is as follows:

1. Drop the existing subset of tables from DEV
2. Create snapshots in the DEV database with the same names as the dropped tables using the following syntax:

create snapshot TABLENAME
build immediate
refresh complete on demand
with rowid
for update
as
select * from TABLENAME_at_PRODDB
nologging
/

3. Build the indexes on these snapshots to match what we had on the dropped tables

4. Gather stats on the snapshots

5. Create a snapshot refresh group which does a complete refresh of the snapshots from the associated master table in PRODDB.

These snapshots need to be updateable in DEV so that they act as regular tables, but I want to overwrite any DEV changes when we refresh the snapshots from the PROD data. My concern is that I want to make sure that no changes from the snapshots in DEV are ever replicated back to the master tables in PROD.

In particular this parameter bothers me (from DBMS_REFRESH.MAKE):

'push_deferred_rpc' (used to control whether updateable snapshots replicate their changes back to master tables during a refresh).

Does this sound like the correct approach to this problem..?

Matt Received on Wed Jul 27 2005 - 05:50:26 CDT