Well, here's what I'm trying to do. I've got a pile of tables that are
read-only tables that are downloaded periodically (approximately daily)
from another system. They get slurped into our database via sqlloader.
A previous incarnation of this process had the setup run sqlloader on the
data table directly. The side-effect was that while it was being loaded,
the data in the table would "disappear" while the data was being loaded.
So the solution (obtained here, actually) was to have two tables FOO_A and
FOO_B, and have a synonym FOO that pointed to either FOO_A or FOO_B.
While FOO_A is "live", then FOO_B gets loaded, and then the synonym
switches, so the new 'table' FOO appears "instantly".
Needing to drop the synonym and recreate it is a two-step process... so
there is always the possibility someone will do a SELECT against FOO in
the instant between the operations. I'm trying to avoid that gap if
possible.
I'd like to stay away from a view, since these tables are heavily used for
reading, and are heavily indexed based on their usage... and a view would
certainly complicate that.
-Rick
On Mon, 2 Jul 2001, Jim Conboy wrote:
> Can you use a view instead?
>
> SVRMGR> create table temp1 (temp1 varchar2(1));
> Statement processed.
> SVRMGR> create table temp2 (temp1 varchar2(1));
> Statement processed.
> SVRMGR> insert into temp1 values ('a');
> 1 row processed.
> SVRMGR> insert into temp1 values ('a');
> 1 row processed.
> SVRMGR> insert into temp1 values ('a');
> 1 row processed.
> SVRMGR> insert into temp1 values ('a');
> 1 row processed.
> SVRMGR> insert into temp2 values ('b');
> 1 row processed.
> SVRMGR> insert into temp2 values ('b');
> 1 row processed.
> SVRMGR> insert into temp2 values ('b');
> 1 row processed.
> SVRMGR> create or replace view temp as select * from temp1;
> Statement processed.
> SVRMGR> select * from temp;
> T
> -
> a
> a
> a
> a
> 4 rows selected.
> SVRMGR> create or replace view temp as select * from temp2;
> Statement processed.
> SVRMGR> select * from temp;
> T
> -
> b
> b
> b
> 3 rows selected.
> SVRMGR>
>
>
> Maybe some unwanted overhead with the view, but it might help out.
>
> Jim
>
>
>
> >>> [EMAIL PROTECTED] 06/30/01 04:56PM >>>
> Is there a way to do what would be an ALTER SYNONYM?
>
> I've got a synonym created that rotates between pointing to two different
> tables. Sometimes it points to TABLE_A, sometimes to TABLE_B. (This is
> so that behind the scenes, I can truncate and reload TABLE_A, and then
> swap, etc. so the table "never disappears".)
>
> However, when I want to switch the SYNONYM from pointing to TABLE_A to
> pointing to TABLE_B, the only way is to:
>
> drop synonym table_syn;
> create synonym table_syn for table_b;
>
> Is there a way to make that instantaneous for the database? If someone
> does a select at the exact instant between those two commands, it'll error
> out, because the table "won't exist".
>
> -Rick
>
> +--------------------------------------------------------------------------+
> | Rick Osterberg [EMAIL PROTECTED] |
> | Database Applications Specialist FAS Computer Services |
> +--------------------------------------------------------------------------+
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rick Osterberg
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
+--------------------------------------------------------------------------+
| Rick Osterberg [EMAIL PROTECTED] |
| Database Applications Specialist FAS Computer Services |
+--------------------------------------------------------------------------+
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rick Osterberg
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
