Re: "ALTER SYNONYM"?

Rick Osterberg wrote:
>
> 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
> >
> >
> >
> > >>> osterber_at_fas.harvard.edu 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,

   It may be a stupid idea but have you considered the possibilities opened by partitioned tables? Exchanging partitions and the like? It is not impossible that you could load and then swap in a single DDL statement - which is what you are after. Not sure it works (too lazy to read the doc) but worth a look IMHO.

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).