Re: Data Warehouse/weekly loads

!! Please do not post Off Topic to this List !!If this is really a "separate database", not just a "separate instance" (i.e. OPS), then there may not be any "really good" options. You still have to move the new data to the "other" database. Replication MIGHT work, but don't take it lightly! Transportable tablespaces, perhaps in conjunction with partitioning, might also work. (And might work really slick with something like EMC BCV's or "transportable" split disk mirrors to move the datafiles about!) What might work best will depend on some unmentioned factors. Are any of the "dynamic tables" the same tables that are being loaded weekly? Is there any essential referential integrity between those two sets of tables? How current must the "dynamic" data be in the database where the weekly loads take place? How large are the dynamic tables? Can they just be totally replaced in the loading database once a week? Are you appending the tables with your weekly loads or replacing them?

On the other hand, if it is really only a "different instance" in an OPS environment, no data movement is necessary. I have done this using a new partition for each of the weekly loads - where the tables being bulk loaded were not also dynamic. That might work well - depending on the implications of rebuilding any global indexes on the partitioned table(s). (I had the luxury of doing the weekly bulk loads and index creation on weekends, during the dead of night, when the users were fast asleep.)

-Don Granaman
[certifiable Orasaurus]

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, September 12, 2001 11:10 PM

> What are the options for being able to keep a data warehouse available
> to users, but still being able
> to do weekly data loads during regular business hours? Our warehouse is
> large enough that we are going to have to create a seperate instance on
> another box to perform the weekly data loads on. However, there are
> some tables that do hold some dynamic data. After performing the weekly
> load, how do you synchronize all the changes on both databases into one
> database so that the users have all the data?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Connie Milliken
> INET: cemail_at_sprintmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Granaman
  INET: granaman_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).