Re: Drop a table via synonym

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 01 Dec 2006 08:40:34 -0800
Message-ID: <1164991232.980688@bubbleator.drizzle.com>

Mark D Powell wrote:
>
> On Nov 30, 7:46 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> nicetom..._at_yahoo.com wrote:
>>> Thanks for yor reply.
>>> As per design,The procedure should drop the table and create the table
>>> each time when run.
>>> drop table A
>>> create table A as select * from table B where 1=2;
>>> So a synonym will not help in this case......
>>> Pls advise.This may be the only strategy in some databases but it is not the
>> only one in Oracle ... it is a bad one.
>>
>> Look into using a GLOBAL TEMPORARY table. There are two types ...
>> one that empties itself ON COMMIT and another that empties
>> itself at the end of the session.
>>
>> Either will give you substantially better performance and
>> scalability.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> Daniel I think you missed the requirement that user A owns the data and
> user B must be able to read it. Unless my memory is wrong Global
> temporary tables do not allow sharing data between users.
>
> I forgot to mention that dropping of user A's table would also drop the
> grant user B needs to select from the table in my first response.
>
> HTH -- Mark D Powell --

If that is the premise then, I agree, a GTT won't work.

That takes us back then, I think, to the bottom-line question of why does the design require buiding and deleting tables on-the-fly.

On reflection I wonder whether user A could provide the data in a permanent table and grant SELECT on that table to B who could then work with it in a GTT.

Perhaps the OP will enlighten us.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Dec 01 2006 - 10:40:34 CST