Re: Making a "copy" of a schema.

michaelloll_at_hotmail.com (Mike) wrote in message news:<cba741c1.0303111028.1a869fcc_at_posting.google.com>...
> Part of a project I am working on is making available a static
> snapshot of the database at a given time inteval - for example, the
> past four (4) weeks of data.
>
> Due to the processing involved, we simply can't query the data and
> specify a date range. One of our developers had the idea of creating
> a new schema (I'm not even sure if that is the correct term) for each
> copy, with a consistent naming convention. Then the appropriate users
> could connect through our front end to whichever "copy" they wanted.
>
> So, something like:
>
> 1) Stored Procedure runs at 2:30AM on a Sunday to create a schema
> called XYZ0312003 for the copy ending on date 03/15/2003.
>
> 2) We put logic into our front end that allows users to connect to
> whichever copy.
>
> Does this sound like a feasible situation? Barring size constraints
> (which are still unknown at this point), does this sound ok? The old
> "copies" wuld only allow READ access, not data updating or inserting.
>
> Thanks in advance.

Mike, multiple schemas might work. Depending on the amount of data, number of tables, and how much time you have to capture the data you might be able to use fromuser= touser= exp/imp jobs to populate it.

Another idea that would require only one schema would be to build duplicate tables that you add a period column to. The period would become part of the PK for all tables and would be included in the queries against the data. When a period passes you delete the old period and insert the new one. There are advantages to the multiple schema such as being able to just trunc all the tables and reload, but you will have to know which schema is what period and they will either always be changing or you will have to migrate user A to B after B went to C. That could be time consuming and would generate a lot of unnecessary redo.

Are you sure partitioning and views cannot be used in the current schema perhaps after adding a few date/period columns? Worth thought if the application is home grown and not purchased.

HTH -- Mark D Powell -- Received on Tue Mar 11 2003 - 19:59:33 CST