My client has two tables that are identical other then name, I'll call them T1 and T2.
One synonym, TN that points to T1, while some batch process works on T2.
When the batch is completed the synonym TN is replaced pointing to the T2 table. This switch takes place back and forth once a day.
My questions are:
1) When create or replace synonym t for t2; is done switching TN to point to t2 instead of t1 and concurrent queries to Oracle accessing TN keeps coming in either by using sql or storedproc , will there be a point where at the time of switching a query may fail.
In general, no. Lookup of synonym object is done at parsing time. Queries done before the "replace" continue to go to T1, and queries after go to T2. Also, procedures using synonyms "should" compile automatically when if they were invalidated after the change
There are rules for "replacing" a synonym, just make sure you can replace the synonym
Also, there could be better designs to your requirement