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).
