Renaming a table on-the-fly

Oracle 8.1.6 on HP-UX 11.0

The application requirements demand that I refresh a medium sized table (2,000,000 rows) once a day. The refresh is a complete replacement. The on-line table is subject to inserts, but not updates, by the application during normal operations.

I have two versions of the table: one is on-line; the other is off-line and the target of the refresh. When the refresh is complete, I "swap" the tables by re-pointing the alias from the on-line table to the off-line table. (Actually, I rename the tables, because the application doesn't understand aliases.)

The application manager wants me to accomplish the swap without bringing the database down, but I'm concerned about errors during the swap:

• inserts just before the rename might cause the rename to fail
• renames just before the insert might cause the insert into the wrong table

Any suggestions on how to accomplish this would be appreciated. Received on Wed Dec 06 2000 - 10:27:18 CST