How to handle large, frequent SQL*Loads?

Every week we receive several 100MB text (ASCII) files with product information. This information needs to be split up and "built" into the database while the existing tables stay online. We can't just drop the old tables and start building the new ones (takes 1-2 hours) as that would result in downtime.

Our current solution...

We have two tables for each table that we need. For example, PROD_INFO1 and PROD_INFO2. A synonym points to the "active" table that has the most up to date data. When we build, we build to the "in-active" table. When we're finished, we move the synonym over to the new table, making it "active" and then truncat'ing the data in the other one.

This isn't too bad (or maybe it is?), except it requires twice as much hard drive space since we essentially allocate room for two of every table.

I'd like to have one large BUILD tablespace, of maybe 1GB or so, that we can use as a scratch area. We build the new tables here, point the synonym over, truncate the old, COPY the the build-area to the correct tablespace, then move the synonym again. Finally, after everyone is out of the build table, it gets truncate'd.

This requires more overhead and turns out to be quite a bit more time consuming. I'm curious-- those who do frequent entire table rebuilds and who must keep the database online, what strategy do you use?

Please note, even the above method involves some downtime as synonyms are switched over (must be dropped and recreated).

Any tips would be greatly apprecited.

Thanks.

Jim. Received on Mon Dec 30 1996 - 00:00:00 CST