| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: "ALTER SYNONYM"?
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:osterber_at_fas.harvard.edu]
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
>
>
>
> >>> 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 Osterberg osterber_at_fas.harvard.edu
|
> | Database Applications Specialist FAS Computer Services
+--------------------------------------------------------------------------+
+--------------------------------------------------------------------------+ | Rick Osterberg osterber_at_fas.harvard.edu | | Database Applications Specialist FAS Computer Services | +--------------------------------------------------------------------------+
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rick Osterberg INET: osterber_at_fas.harvard.edu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daemen, Remco INET: R.Daemen_at_facent.nl 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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).Received on Thu Jul 05 2001 - 12:26:07 CDT
 |
 |