Re: Oracle Table Rename

Wesley M. Stephens wrote:
>
> I have a situation in which I would like to maintain two identical
> tables (same definition, but different names). I would like to have
> applications reference one or the other of these tables through either a
> synonym or a view. For example, I would like to have synonym T and have
> it point to either table T1 or T2. Whenever the synonym T points to T1,
> all application queries would be using the data in T1. During a window
> of serveral hours every night I will be truncating and loading new data
> into table T2. When that load is complete I would like to redirect the
> synonym to point to T2 so that applications would immediately get the
> new data. In this fashion I hope to minimize the time the table is
> "unavailable" during the reload.
>
> My problem is of course that re-establihing the synonym to point to the
> new table invalidates all the procedures etc. that use the synonym.
> What I need is a mechanims for accomplishing this sleight-of-hand that
> does not invalidate anything?
>
> Does anyone have any ideas? This is a simple process with a file system
> in that files can simply be renamed. I can't, however, come up with an
> approach that looks like it will work with Oracle...it's working
> entirely too hard to protect me from myself...
>

ORACLE has a "RENAME <table_name_before> TO <table_name_after>" command that does what we need in a situation similar to yours. We have identical tables ("...CURR" and "...PREV") that are referred to by views ("...CURR_VW" and "...PREV_VW"). Each month, we replace the "...PREV" table's data and then "flip-flop" the names as follows to make the previous "current" data become the current "previous" data and the current "previous" data (that was just reloaded) become the new "current" data. (Got that?!)

   RENAME ...CURR TO ...TEMP;
   RENAME ...PREV TO ...CURR;
   RENAME ...TEMP TO ...PREV;

We never do anything to the views. When the RENAMEs are done, our views "...CURR_VW" and "...PREV_VW" are invalid, but they automatically are "validated" the first time they're referenced, after a slight one-time delay. The views refer by name to the underlying tables. When "vaidated", tables with the expected names exist, so all goes well. We don't have any dependent procedures or functions, but I'd expect they'd work the same way. As for synonyms, the table they refer to doesn't even have to exsit, so as longh as there's something with the name they're pointing to when the synonyms are referenced things should be OK.

+================+========================================+
| Rick Wiggins   | Phone: (910)279-2270                   |
| Lead Developer | FAX:   (910)279-2561                   |
| AT&T HRISO     | Internet:   mailto:rickwiggins_at_att.com |
| Greensboro, NC | CompuServe: rick_wiggins               |
+================+========================================+