| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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:
-----Original Message-----
From: H, Linda [mailto:lindah_at_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_at_hoffman.army.mil
<mailto:kirschw_at_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:
Any suggestions on how to accomplish this would be appreciated.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.comReceived on Thu Dec 07 2000 - 10:00:33 CST
<http://www.orafaq.com>
-- Author: Kirsch, Walter J, PERSCOM INET: kirschw_at_hoffman.army.mil ------_=_NextPart_001_01C06066.B07FC78E Content-Type: text/html; charset="ISO-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<TITLE>RE: Renaming a table on-the-fly</TITLE>
<META content="MSHTML 5.00.2314.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>Hi
Linda,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>
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:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>
- sqlldr refresh data into table TABLEz</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>
- rename TABLEx to TABLEy</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>
- rename TABLEz to TABLEx</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>
- recompile triggers, stored procedures</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=056344515-07122000>
- the next time thru, interchange "y" and "z" above, etc.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=056344515-07122000>Thanks, Jim</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> H, Linda [mailto:lindah_at_epocrates.com]<BR><B>Sent:</B> Wednesday, December 06, 2000 1:58 PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: Renaming a table on-the-fly<BR><BR></DIV></FONT> <P><FONT size=2>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. </FONT></P> <P><FONT size=2>Regards, Linda </FONT><BR><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: Kirsch, Walter J, PERSCOM [<A href="mailto:kirschw_at_hoffman.army.mil">mailto:kirschw_at_hoffman.army.mil</A>]</FONT> <BR><FONT size=2>Sent: Wednesday, December 06, 2000 8:31 AM</FONT> <BR><FONT size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT size=2>Subject: Renaming a table on-the-fly</FONT> </P><BR> <P><FONT size=2>Oracle 8.1.6 on HP-UX 11.0</FONT> </P> <P><FONT size=2>The application requirements demand that I refresh a medium sized table</FONT> <BR><FONT size=2>(2,000,000 rows) once a day. The refresh is a complete replacement. The</FONT> <BR><FONT size=2>on-line table is subject to inserts, but not updates, by the application</FONT> <BR><FONT size=2>during normal operations.</FONT> </P> <P><FONT size=2>I have two versions of the table: one is on-line; the other is off-line and</FONT> <BR><FONT size=2>the target of the refresh. When the refresh is complete, I "swap" the</FONT> <BR><FONT size=2>tables by re-pointing the alias from the on-line table to the off-line</FONT> <BR><FONT size=2>table. (Actually, I rename the tables, because the application doesn't</FONT> <BR><FONT size=2>understand aliases.)</FONT> </P> <P><FONT size=2>The application manager wants me to accomplish the swap without bringing the</FONT> <BR><FONT size=2>database down, but I'm concerned about errors during the swap:</FONT> <BR><FONT size=2> - inserts just before the rename might cause the rename to fail</FONT> <BR><FONT size=2> - renames just before the insert might cause the insert into the wrong</FONT> <BR><FONT size=2>table</FONT> </P> <P><FONT size=2>Any suggestions on how to accomplish this would be appreciated.</FONT> </P> <P><FONT size=2>-- </FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com</A></FONT> <BR><FONT size=2>-- </FONT><BR><FONT size=2>Author: Kirsch, Walter J, PERSCOM</FONT> <BR><FONT
 |
 |