Table behind synonym does not exist

oraclesynonymstable

when I do

select *
from all_synonyms
where synonym_name='SYNAAA'

I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A.

Given SYNAA is public, I should be able to query it directly by just select * from SYNAA right? but I got "table or view does not exist" error, I tried to refer it by schemaname.SYNAAA or schemaname.TABAAA, or grant but all failed.

The thing is when I check schema A's table list, there is no table TABAAA under it. Is this the reason I got "table or view does not exist" error? table behind the synonym SYNAA does not exist?

Thank you very much!

Best Answer

A synonym is an alternative name for an object.

From Database SQL Language Reference

Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.

So the synonym is an additional name that you can use to access the object. But you must have still appropriate grants on the base object.

If SYNAAA is a public synonym for the table A.TABAAA and you get an error when you execute

select * from SYNAAA

then you will get the same error if you execute

select * from A.TABAAA

the error 

ORA-00942 table or view does not exist

means either the table A.TABAAA exists but you have no privileges on it or the table does not exist at all. The view all_tables doe not show you all tables of the database but only the tables that you can access. Generally you cannot check if a table does not exist at all nor can you grant yourself privileges on a table of another schema. A DBA can query the dba_tables view to check if the table exists.

select owner, table_name
from dba_tables
where owner='A'
and table_name='TABAAA'

If Oracle raises an error message and you post this here then you hould always post the complete error message, which is

ORA-00942 table or view does not exist

An explanation of the error message can be found in the documentation of your database version, in the documentation of the current version. If there is no explanation you should try the documentation of Oracle 9i. For this error message the 9i manual says

(...) Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.

Related Question