Message-Id: <10703.123934@fatcity.com> From: "Kirsch, Walter J, PERSCOM" Date: Thu, 7 Dec 2000 11:00:33 -0500 Subject: RE: Renaming a table on-the-fly This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C06066.B07FC78E Content-Type: text/plain; charset="ISO-8859-1" Hi Linda, It's not the refresh that's worrying me; rather, the application must reference whatever table is currently on-line by one name: TABLEx. I have to manage the database by making sure that TABLEx points to whichever of the two tables that has been refreshed most-recently. The target of the refresh data is, of course, the table that has been refreshed least-recently. The refresh data comes on tape in sqlldr format. The cycle is: - sqlldr refresh data into table TABLEz - rename TABLEx to TABLEy - rename TABLEz to TABLEx - recompile triggers, stored procedures - the next time thru, interchange "y" and "z" above, etc. Thanks, Jim -----Original Message----- From: H, Linda [mailto:lindah@epocrates.com] Sent: Wednesday, December 06, 2000 1:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: Renaming a table on-the-fly Could a snapshot replace the rename process? Both tables would be available at all times, and the snapshot delta update could be executed every few minutes/hours, as required. Regards, Linda -----Original Message----- From: Kirsch, Walter J, PERSCOM [ mailto:kirschw@hoffman.army.mil ] Sent: Wednesday, December 06, 2000 8:31 AM To: Multiple recipients of list ORACLE-L Subject: 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kirsch, Walter J, PERSCOM INET: kirschw@hoffman.army.mil ------_=_NextPart_001_01C06066.B07FC78E Content-Type: text/html; charset="ISO-8859-1" RE: Renaming a table on-the-fly
Hi Linda,
  It's not the refresh that's worrying me; rather, the application must reference whatever table is currently on-line by one name: TABLEx.  I have to manage the database by making sure that TABLEx points to whichever of the two tables that has been refreshed most-recently.  The target of the refresh data is, of course, the table that has been refreshed least-recently.  The refresh data comes on tape in sqlldr format. The cycle is:
  - sqlldr refresh data into table TABLEz
  - rename TABLEx to TABLEy
  - rename TABLEz to TABLEx
  - recompile triggers, stored procedures
  - the next time thru, interchange "y" and "z" above, etc.
Thanks, Jim
-----Original Message-----
From: H, Linda [mailto:lindah@epocrates.com]
Sent: Wednesday, December 06, 2000 1:58 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Renaming a table on-the-fly

Could a snapshot replace the rename process?  Both tables would be available at all times, and the snapshot delta update could be executed every few minutes/hours, as required.

Regards, Linda
-----Original Message-----
From: Kirsch, Walter J, PERSCOM [mailto:kirschw@hoffman.army.mil]
Sent: Wednesday, December 06, 2000 8:31 AM
To: Multiple recipients of list ORACLE-L
Subject: 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.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kirsch, Walter J, PERSCOM