Re: How to access Synonym when it has same name as that of a Table

On Dec 24, 8:46 am, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> basab.da..._at_gmail.com wrote:
> > When execute the SQL ' Select * from BE_FRIEND' it always returns the
> > data in the table. How to distinguish between a Table and a Synonym?
>
> Not - that is the purpose of a public synonym.
>
> If you are referring to the object type (as in
> "I want to know if be_friend ia a table, or a synonym
> or both"), then query the all_objects view on object_name
> and object_type.
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...

Unfortunately I do not have a system in front of me to test with so I can post a demo but try the following.

If you are a different user from the table owner then try select * from owner.be_friend. Fully qualifing the table as owner.table_name should avoid referencing the public synonym.

Generally speaking you should not be defining public synonyms with the same name as an existing application object where the synonym references an object with a different name than the synonym name. That is, if the application table is prod.partsmaster then the public synonym would be partsmaster. Sometimes you may want the name to be different but most of the time it should be the same. Where different objects need to be accessed using the same name you probably need to use private rather than public synonyms.

If a user defines a private synonym partsmaster that points to test.partsmaster then for that user his or her queries will find the private synonym before looking for a public synonym. Also if this user owns a table or view named partsmaster that table will be seen before the public synonym.

HTH -- Mark D Powell -- Received on Mon Dec 24 2007 - 16:23:33 CST