Can DBA create a public synonym for a table that has the same name as an existing table Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr. 

If a user performs a query like the following statement: 

select * from hr; 


What will be returned by the query? 

Whose table values will be shown?  


The records of the public synonym  
The records of the table
The user table will take precedence over the public synonym.
The data from the user table would be shown.

Oracle's object name resolution is explained in the article Managing Object Name Resolution (Oracle Database Administrators Guide)

The following SQL code will explain the use case:

SQL&gt; conn user1
Enter password:
Connected.

SQL&gt; create table test as select * from user_objects where 1=2; --Creating first table with 0 rows 
Table created.

SQL&gt; create table conflicting_name as select * from user_objects where rownum&lt;2; --Creating second table with 1 row
Table created.

SQL&gt; select count(0) from test; --first table with 0 rows 
  COUNT(0)
----------
         0

SQL&gt; select count(0) from conflicting_name; --second table with 1 row
  COUNT(0)
----------
         1


At this stage you have the test table containing zero (0) rows and the conflicting_name table containing one (1) row.

Let's continue by creating a public synonym and querying conflicting_name:

SQL&gt;  create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.


SQL&gt; select count(0) from conflicting_name; --second table still points to original table and public synonym not used 
  COUNT(0)
----------
         1


The result shows you that the query is run against the table conflicting_name which contains one row and not against the public synonym conflicting_name which references the test table containing zero rows.

What happens with a different user? Let's try:

SQL&gt; conn user2 
Enter password:
Connected.

SQL&gt; -- Connected to different user 

SQL&gt; select count(0) from conflicting_name; --now public synonym pointing to first table kicks in 
  COUNT(0)
----------
         0

SQL&gt; select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym 
  COUNT(0)
----------
         1


Because of the public synonym, user2 will be querying the test table. Only if user2 directly queries the user1.conflicting_name table will the single row be returned.