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).
RE: ALTER SYNONYM?
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
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:[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
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 [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, 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: [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).
RE: ALTER SYNONYM?
Although I have not used their software, I recall that Platinum's Desktop DBA has a feature for 'alter synonym. -Original Message- Sent: Sunday, July 01, 2001 1:21 PM To: Multiple recipients of list ORACLE-L Rick: No. There is no alter synonym command in SQL (none that I can find or know of, at least). The way you describe is the only way to do it. -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, June 30, 2001 4:56 PM 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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: Norwood Bradly A 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).
Re: ALTER SYNONYM?
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-4 rows selected.SVRMGR create or replace view temp as select * from temp2;Statement processed.SVRMGR select * from temp;T-bbb3 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 differenttables. Sometimes it points to TABLE_A, sometimes to TABLE_B. (This isso that behind the scenes, I can truncate and reload TABLE_A, and thenswap, etc. so the table "never disappears".)However, when I want to switch the SYNONYM from pointing to TABLE_A topointing 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 someonedoes a select at the exact instant between those two commands, it'll errorout, 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: ALTER SYNONYM?
The problem with this would be any dependent objects on the view would need to be recompiled whenever the view was created. How about a synonym to a periodically recreated view? Ron. [EMAIL PROTECTED] [EMAIL PROTECTED] Karaoke: Japanese for migraine. Jim.Conboy@tr w.comTo: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: Re: ALTER SYNONYM? com 07/02/01 08:35 AM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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
Re: ALTER SYNONYM?
Rick: No. There is no alter synonym command in SQL (none that I can find or know of, at least). The way you describe is the only way to do it. -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, June 30, 2001 4:56 PM 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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).
ALTER SYNONYM?
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).