Rick,
If your current process works for you, that's great!
My only thought was that, since you are already sqlloading the data, why not
right a PL/SQL procedure that would process the transactions into the
existing table.
For example, read a record from the sqlloader table, look for a matching
record in the FOO table. If found, either delete then insert the record, or
simply update the data. If not found, insert a new record.
This way, the existing table never disappears. Users keep on querying data
as if nothing is happening. Everybody is happy. You didn't mention how
many records you were talking about here, and that might impact whether this
is a good idea or not (if you are talking multi-millions of records, then
this is a bad idea! :) )
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, July 05, 2001 1:05 PM
To: Multiple recipients of list ORACLE-L
Hi Rick,
How about creating a private synonym for FOO_A and a public synonym for
FOO_B. When you drop the private synonym, the public synonym takes effect
immediately, and when FOO_A is ready for usage again, you can create a new
private synonym. That way there's always a table available.
Just a thought ...
HTH, Remco
-----Oorspronkelijk bericht-----
Van: Rick Osterberg [mailto:[EMAIL PROTECTED]]
Verzonden: donderdag 5 juli 2001 18:06
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: "ALTER SYNONYM"?
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
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).