Re: Snapshot Rep. between 2 schemas in same instance

Grant Fitzgerald wrote:
>
> Hi,
>
> I need to have two copies of my data within one instance. One
> copy is updateable by an admin user and the other copy is read-
> only by other users. The problem is that I want to allow the
> admin user to update the data without the others being affected
> until the admin user decides that the new data is to be made
> available i.e. normal users work with the old data until the
> admin user is finished entering the new data.
>
> My initial idea is to use read-only snapshot replication to
> provide the normal users with snapshots of the admin user's
> data. Then, when the admin user finishes entering new data a
> refresh can be done and all the other users can then see the new
> data.
>
> Is this a sensible way to do things? Should I be using a second
> instance? This would no doubt increase the licensing costs? I am
> relatively new to Oracle so am not sure if there is a better way
> to achieve what I need.
>
> Thanks for any advice,
> Grant Fitzgerald

Works like a charm; use materialized views (or snapshots, as they used to
be called) in one schema. This schema will be used by the normal users. Update the original data (in the admin schema, or schemas), and re-create
the mat. views - no need to mat. view logs, either.

If you have a huge amount of data, it may be faster on your refreshes to use materialized view logs (or snapshot logs); only the alterations are applied to the mat. views, as opposed to the scenario above. Keep in mind a
materialized view is created as fast as a 'create table X as select ... from table Y'

And a second instance will not add $$$ to your licence fee (assuming you have a per-machine licence), but it does put a burden on memory, processes,
etc.

-- 
Gtrz,

Frank van Bortel