Re: Snapshot Rep. between 2 schemas in same instance

Why not have a column in each table called readable_flag that defaults to 0. The admin users can enter their data and when they are ready to let the other users see their data, they can update each table and set readable_flag to 1.

Then create views based on these tables that do a select * from tablename where readable_flag = 1. Give the ordinary users select privileges on these views. This means that you don't need a second schema or a second database.

Regards
"Frank van Bortel" <fbortel_at_home.nl> wrote in message news:3C2771E0.3C36931B_at_home.nl...
> 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
Received on Mon Dec 24 2001 - 23:58:37 CET