RE: How does oracle resolve a reference to an object

Hi Gene,

Private synonyms and tables live in the same namespace. That is, for any given user, said user can have a table named TABLE or a private synonym named TABLE, but not both. Oracle tries to resolve names in the current namespace first, so, if there is a table or private synonym owned by user, oracle will first use that, if not, it will look for a public synonym named TABLE and use that. Consider also that USER can use USER2's namespace to resolve objects (within the limits of object permissions) by using the "ALTER SESSION SET CURRENT_SCHEMA user2;" command. In this case, Oracle will search USER2's namespace, then fall back to PUBLIC. In this case, objects in the USER namespace would not be seen.

Hope that helps,

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
Sent: Monday, May 15, 2006 5:12 PM
To: oracle-l_at_freelists.org
Subject: How does oracle resolve a reference to an object

Hi all:

If I have a table named TABLE in a database, a public synonym named TABLE that points to this database and a private synonym TABLE that points to a table called TABLE2, what is the order in which these objects will be picked by Oracle? Is that - private synonysm followed by public synonym followed by the table?

In other words if I execute select * from TABLE and I have a private synonym TABLE that points to TABLE2, will that SQL go against the TABLE2? thank you

Gene Gurevich

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 15 2006 - 16:21:06 CDT