Re: Drop a table via synonym

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 -- Received on Fri Dec 01 2006 - 08:18:28 CST